SQL分组返回表的所有列
declare @table1 table (id int,price int,starttime datetime, orderid int)insert into @table1select 1,50,'2012-5-1',1union select 2,100,'2012-5-1',1union select 3,50,'2012-5-1',2union select 4,60,'2012-5-1',2union select 5,70,'2012-5-1',2union select 6,70,'2012-5-1',3union select 7,90,'2012-5-1',3 ;witha as ( select orderid,max(price) as maxprice from @table1 group by orderid)select [@table1].* from @table1,a where [@table1].orderid = a.orderid and [@table1].price = a.maxprice
declare @table1 table (id int,price int,starttime datetime, orderid int)insert into @table1select 1,50,'2012-5-1',1union select 2,100,'2012-5-1',1union select 3,50,'2012-5-1',2union select 4,60,'2012-5-1',2union select 5,70,'2012-5-1',2union select 6,70,'2012-5-1',3union select 7,90,'2012-5-1',3 union select 8,90,'2012-5-1',3 ;--1SELECT * FROM @table1 a WHERE NOT EXISTS(SELECT 1 FROM @table1 b WHERE a.orderid=b.orderid AND a.price<b.price)--SELECT * FROM @table1 a WHERE NOT EXISTS(SELECT 1 FROM @table1 b WHERE a.orderid=b.orderid AND a.price<b.price OR (a.orderid=b.orderid AND a.id<b.id))--2SELECT * FROM @table1 a WHERE 1>(SELECT COUNT(*) FROM @table1 b WHERE a.orderid=b.orderid AND a.price<b.price)--3;WITH t AS(SELECT *,row=ROW_NUMBER()OVER(PARTITION BY orderid ORDER BY price DESC) FROM @table1)SELECT * FROM t WHERE row=1
