日期:2014-05-18  浏览次数:20693 次

问个SQL语句
ProductID CustID Time
a 1 2007-08-01
a 1 2007-09-01
b 1 2007-09-01 

如果 ProductID有重复的 只取Time最大的那条纪录

上面的要取出
ProductID CustID Time
a 1 2007-09-01
b 1 2007-09-01

------解决方案--------------------
--方法二

create table tb (ProductID varchar(10),CustID int,Time datetime)
insert into tb values('a',1,'2007-08-01')
insert into tb values('a',1,'2007-09-01')
insert into tb values('b',1,'2007-09-01') 
go

select a.* from tb a where time = (select max(time) from tb where productid = a.productid)

drop table tb

/*
ProductID CustID Time
---------- ----------- -----------------------
b 1 2007-09-01 00:00:00.000
a 1 2007-09-01 00:00:00.000

(所影响的行数为 2 行)
*/