MSSQL2000取最后日期物料数据(有重复的前提下)解决思路

MSSQL2000取最后日期物料数据(有重复的前提下)
MSSQL2000 取goodsid,最大日期(可能重复),再取USERDEF1大值者,

billdate , goodsid, userdef1, userdef2, userdef3, userdef4
2011-11-01 , 1001 , a ,b , c, d
2011-11-01 , 1001 , b , c , d ,e

2011-11-01 , 3002 , c ,d ,e ,f
2011-11-08 , 3002 , e ,f ,g ,h

2011-11-11 , 3013 , e ,f ,g ,h


要求结果如下： 相同GOODSID的行只取一行,不要重复
billdate , goodsid, userdef1, userdef2, userdef3, userdef4
2011-11-01 , 1001 , b , c , d ,e
2011-11-08 , 3002 , e ,f ,g ,h
2011-11-11 , 3013 , e ,f ,g ,h



[解决办法]

SQL code

select * from tb t where billdate=(select max(billdate) from tb where  goodsid=t.goodsid)
[解决办法]
SQL code
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (billdate datetime,goodsid int,userdef1 nvarchar(2),userdef2 nvarchar(2),userdef3 nvarchar(2),userdef4 nvarchar(2))insert into [TB]select '2011-11-01',1001,'a','b','c','d' union allselect '2011-11-01',1001,'b','c','d','e' union allselect '2011-11-01',3002,'c','d','e','f' union allselect '2011-11-08',3002,'e','f','g','h' union allselect '2011-11-11',3013,'e','f','g','h'select * from [TB]SELECT * FROM TB WHERE EXISTS(SELECT 1             FROM TB A             WHERE TB.goodsid = goodsid AND TB.USERDEF1 >USERDEF1 )                        /*billdate    goodsid    userdef1    userdef2    userdef3    userdef42011-11-01 00:00:00.000    1001    b    c    d    e2011-11-08 00:00:00.000    3002    e    f    g    h*/
[解决办法]
SQL code
--小F这个好，有单一值的时候就好用select * from tb t where userdef1=(select max(userdef1) from tb where  goodsid=t.goodsid)ORDER BY t.billdate ASC/*billdate    goodsid    userdef1    userdef2    userdef3    userdef42011-11-01 00:00:00.000    1001    b    c    d    e2011-11-08 00:00:00.000    3002    e    f    g    h2011-11-11 00:00:00.000    3013    e    f    g    h*/