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]
go
create 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 all
select '2011-11-01',1001,'b','c','d','e' union all
select '2011-11-01',3002,'c','d','e','f' union all
select '2011-11-08',3002,'e','f','g','h' union all
select '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    userdef4
2011-11-01 00:00:00.000    1001    b    c    d    e
2011-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    userdef4
2011-11-01 00:00:00.000    1001    b    c    d    e
2011-11-08 00:00:00.000    3002    e    f    g    h
2011-11-11 00:00:00.000    3013    e    f    g    h*/

这个事情,关键是同一日期,同一GOODSID可能有重复的,如何可以快速查询呢?

[Quote=引用 3 楼 orchidcat 的回复:]
SQL code
--小F这个好，有单一值的时候就好用
select * from tb t where userdef1=(select max(userdef1) from tb where goodsid=t.goodsid)
ORDER BY t.billdate ASC



谢谢你们的回复,但如果USERDEF1也相同时如何取数呢???

关注一下,billdate , goodsid, userdef1均相同的重复行,如何处理呢?

要有效率就增加一个主健

SQL code

select * from tb as a 
where not exists(select 1 from tb where goodsid=a.goodsid and 
(billdate>a.billdate or (billdate=a.billdate and userdef1>a.userdef1)
or (billdate=a.billdate and userdef1=a.userdef1 and userdef2>a.userdef2)
or (billdate=a.billdate and userdef1=a.userdef1 and userdef2=a.userdef2 and userdef3>a.userdef3)
or (billdate=a.billdate and userdef1=a.userdef1 and userdef2=a.userdef2 and userdef3=a.userdef3 and userdef4>a.userdef4)
)
)

如果全部列都有重复，在select 再加上distinct

distinct 这个好用