SQL2000求最近日期数据行
mssql2000 最近数据行
视图内容:VIEW_GOODSSTORE
goodsid,billdate,STOREID,STORESTATEID
74 ,2011-2-19, 11, ,104
74 ,2011-6-14, 11, ,104
78, 2011-11-11, 12, , 33
91 ,2011-6-19, 11, ,104
91 ,2011-6-19, 11, ,105
91 ,2011-6-19, 11, ,106
结果：
当goodsid相面时，取最大日期的行 (取出来后不要重复的)
goodsid,billdate,STOREID,STORESTATEID
74 ,2011-6-14, 11, ,104
78, 2011-11-11, 12, , 33
91 ,2011-6-19, 11, ,106
[解决办法]
select * from VIEW_GOODSSTORE a
where not exsits(select 1 from VIEW_GOODSSTORE where goodsid=a.goodsid and billdate>a.billdate)
[解决办法]
SELECT goodsid,max(billdate),max(STOREID),max(STORESTATEID)
from VIEW_GOODSSTORE
group by goodsid
[解决办法]
早知道我答了算了。
SELECT *
FROM VIEW_GOODSSTORE a
WHERE EXISTS ( SELECT 1
FROM ( SELECT MAX(billdate) billdate ,
goodsid
FROM VIEW_GOODSSTORE
GROUP BY goodsid
) b
WHERE a.billdate = b.billdate
AND a.goodsid = b.goodsid )
