今天在csdn发帖求助了一段sql代码
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代码示例,感觉写的还是不够好,大虾们有什么更加好的方法吗?”
大虾米们果断给力
第一种方法是使用exists利用@table1表和自己进行查询
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 )
改良的写法为
select * from @table1 a where not exists(select 1 from @table1 where orderid=a.orderid and price>a.price)
第二种比较高深的写法为
WITH t AS ( SELECT *,row=ROW_NUMBER()OVER(PARTITION BY orderid ORDER BY price DESC) FROM @table1 ) SELECT * FROM t WHERE row=1
简单介绍下
partition by关键字是oracle中分析性函数的一部分,它和聚合函数不同的地方在于它能返回一个分组中的多条记录,而聚合函数一般只有一条反映统计值的记录,partition by用于给结果集分组
row_number函数的用途是非常广泛,这个函数的功能是为查询出来的每一行记录生成一个序号
使用row_number函数是要使用over子句选择对某一列进行排序,然后才能生成序号。
我们可以使用row_number函数来实现查询表中指定范围的记录,可以查询t_table表中第2条和第3条记录:
其它编号函数
rank函数考虑到了over子句中排序字段值相同的情况
dense_rank函数的功能与rank函数类似,只是在生成序号时是连续的,而rank函数生成的序号有可能不连续。
ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。ntile函数为每条记 录生成的序号就是这条记录所有的数组元素的索引(从1开始)。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数,用来指定桶数。