declare @table1 table (id int,price int,starttime datetime, orderid int)
insert into @table1
select 1,50,'2012-5-1',1
union select 2,100,'2012-5-1',1
union select 3,50,'2012-5-1',2
union select 4,60,'2012-5-1',2
union select 5,70,'2012-5-1',2
union select 6,70,'2012-5-1',3
union select 7,90,'2012-5-1',3 ;
with
a 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
我想根据@table1中的orderid进行分组,返回最大出价(price)的记录的所有列,以上是我写的sql代码示例,感觉写的还是不够好,大虾们有什么更加好的方法吗?
insert into @table1
select 1,50,'2012-5-1',1
union select 2,100,'2012-5-1',1
union select 3,50,'2012-5-1',2
union select 4,60,'2012-5-1',2
union select 5,70,'2012-5-1',2
union select 6,70,'2012-5-1',3
union select 7,90,'2012-5-1',3 ;
with
a 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
我想根据@table1中的orderid进行分组,返回最大出价(price)的记录的所有列,以上是我写的sql代码示例,感觉写的还是不够好,大虾们有什么更加好的方法吗?
where not exists(select 1 from @table1 where orderid=a.orderid and price>a.price)
insert into @table1
select 1,50,'2012-5-1',1
union select 2,100,'2012-5-1',1
union select 3,50,'2012-5-1',2
union select 4,60,'2012-5-1',2
union select 5,70,'2012-5-1',2
union select 6,70,'2012-5-1',3
union select 7,90,'2012-5-1',3 ;
select * from @table1 a WHERE EXISTS (SELECT 1 FROM (select orderid,max(price)as maxprice from @table1 group by orderid) b WHERE a.orderid=b.orderid
AND a.price=b.maxprice )
/*
(7 行受影响)
id price starttime orderid
----------- ----------- ----------------------- -----------
2 100 2012-05-01 00:00:00.000 1
5 70 2012-05-01 00:00:00.000 2
7 90 2012-05-01 00:00:00.000 3
(3 行受影响)
*/
insert into @table1
select 1,50,'2012-5-1',1
union select 2,100,'2012-5-1',1
union select 3,50,'2012-5-1',2
union select 4,60,'2012-5-1',2
union select 5,70,'2012-5-1',2
union select 6,70,'2012-5-1',3
union select 7,90,'2012-5-1',3
union select 8,90,'2012-5-1',3
;
--1
SELECT * 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))--2
SELECT * 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
declare @table1 table (id int,price int,starttime datetime, orderid int)
insert into @table1
select 1,50,'2012-5-1',1 union all
select 2,100,'2012-5-1',1 union all
select 3,50,'2012-5-1',2 union all
select 4,60,'2012-5-1',2 union all
select 5,70,'2012-5-1',2 union all
select 6,70,'2012-5-1',3 union all
select 7,90,'2012-5-1',3 union all
select 8,90,'2012-5-1',3
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)
)