取sql分组中的某几行数据

《取sql分组中的某几行数据》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、取SQL分组中的某几行数据 对表中数据分组，有时只需要某列的聚合值；有时却需要返回整行数据，常用的方法有：子查询、ROW_NUMBER、APPLY，总体感觉还是ROW_NUMBER比较直观。测试数据：  ifOBJECT_ID('testGroup')isnotnulldroptabletestGroupGOcreatetabletestGroup(IDintidentityprimarykey,UserIDint,OrderIDint) GOinserttestGroup select1,10unionallselect1,20unio

2、nallselect1,30unionallselect2,100unionallselect2,200unionallselect3,1000unionallselect3,2000unionallselect3,3000unionallselect3,4000   一.取分组中第1行(最大/最小值)1.取出分组中某列最大/最小值，不要求显示其他列最常见的分组聚合，用groupby分组时，只有参加分组/聚合的列才可以被显示。  selectUserID,MAX(OrderID)asMaxOrderIDfromtestGroup grou

3、pbyUserID  2.取出分组中某列最大/最小值，要求显示其他列 要显示表中其他列，用groupby不好实现，可以借助子查询。  select*fromtestGroupa whereID=(selectMAX(ID)fromtestGroupbwherea.UserID=b.UserID)orderbyID--或者select*fromtestGroup whereIDin(selectMAX(ID)fromtestGroupgroupbyUserID)--或者select*fromtestGroupasa wherea.IDin(

4、selecttop1IDfromtestGroupbwherea.UserID=b.UserIDorderbyb.OrderIDdesc)--或者select*fromtestGroupawherenotexists(select1fromtestGroupbwherea.UserID=b.UserIDanda.OrderID

5、   二.取分组中前N行(排名前几名)前N行为正向排序(ASC)，后N行改为反向排序(DESC)即可，N=1时也就是取最大/最小值的行。下面以前2名(N=2)为例。1.SQLServer2000的写法(1)子查询  select*fromtestGroupasawherea.IDin(selecttop2IDfromtestGroupbwherea.UserID=b.UserIDorderbyb.OrderID)--或者select*fromtestGroupawherenotexists(select1fromtestGroupbwhe

6、rea.UserID=b.UserIDanda.OrderID>b.OrderID havingcount(1)>=2)--或者select*fromtestGroupawhere(selectcount(1)fromtestGroupbwherea.UserID=b.UserIDanda.ID>=b.ID)<=2--没有唯一标识的表，可以用checksum来标识每行select*fromtestGroupasawherechecksum(*)in(selecttop2checksum(*)fromtestGroupbwherea.Use

7、rID=b.UserIDorderbyb.OrderID)  2.SQLServer2005新语法 (2)ROW_NUMBER()  selectID,UserID,OrderIDfrom (select*,ROW_NUMBER()over(partitionbyUserIDorderbyOrderID)numfromtestGroup)twheret.numbetween1and2  (3)APPLY(TOP)  selectdistinctt.*fromtestGroupacrossapply(selecttop2ID,UserID,

8、OrderIDfromtestGroupbwherea.UserID=b.UserIDorderbyb.OrderID)ast 三.取分组中第N行(排名第N名)把上面的查询中，范围值都改为固定