欢迎来到天天文库
浏览记录
ID:8812645
大小:15.72 KB
页数:4页
时间:2018-04-08
《取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.OrderID5、 二.取分组中前N行(排名前几名)前N行为正向排序(ASC),后N行改为反向排序(DESC)即可,N=1时也就是取最大/最小值的行。下面以前2名(N=2)为例。1.SQLServer2000的写法(1)子查询 select*fromtestGroupasawherea.IDin(selecttop2IDfromtestGroupbwherea.UserID=b.UserIDorderbyb.OrderID)--或者select*fromtestGroupawherenotexists(select1fromtestGroupbwhe6、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.Use7、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名)把上面的查询中,范围值都改为固定
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名)把上面的查询中,范围值都改为固定
此文档下载收益归作者所有