日期:2014-05-17  浏览次数:20592 次

求下方SQL文简化写法
SELECT DISTINCT
  At.GOOD_NO ,
  ( SELECT TOP 1
  IN_Date
  FROM dbo.VM_InStock S
  WHERE S.GOOD_NO = At.GOOD_NO
  ORDER BY IN_Date DESC
  ) IN_Date ,
  ( SELECT TOP 1
  Price
  FROM dbo.VM_InStock S
  WHERE S.GOOD_NO = At.GOOD_NO
  ORDER BY IN_Date DESC
  ) Price
FROM dbo.VM_InStock At
WHERE IN_Type = 1

------解决方案--------------------
SQL code
SELECT TOP 1 At.GOOD_NO ,IN_Date,Price
FROM    dbo.VM_InStock At INNER JOIN  dbo.VM_InStock S ON S.GOOD_NO = At.GOOD_NO
WHERE   at.IN_Type = 1
ORDER BY s.IN_Date DESC

------解决方案--------------------
--如果同GOOD_NO、同IN_Date没有重复数据,那么可以用下面的
select GOOD_NO, IN_Date, Price
from dbo.VM_InStock t1 
where not exists(select 1 from dbo.VM_InStock t2 where t2.GOOD_NO = t1.GOOD_NO and t2.IN_Date > t1.IN_Date)
--其实,最严谨的就是你那个top 1...