取SQL分组中的某几行数据.docx

取SQL分组中的某几行数据对表中数据分组，有时只需要某列的聚合值；有时却需要返回整行数据，常用的方法有：子查询、ROW_NUMBER、APPLY，总体感觉还是ROW_NUMBER比较直观。测试数据：if OBJECT_ID(testGroup) is not nulldrop table testGroupGOcreate table testGroup(ID int identity primary key,UserID int,OrderID int)GOinsert testGroupselect 1,10 union allselect 1,20 union allselect 1,30 union allselect 2,100 union allselect 2,200 union allselect 3,1000 union allselect 3,2000 union allselect 3,3000 union allselect 3,4000一. 取分组中第1行(最大/最小值)1. 取出分组中某列最大/最小值，不要求显示其他列最常见的分组聚合，用group by 分组时，只有参加分组/聚合的列才可以被显示。select UserID, MAX(OrderID) as MaxOrderIDfrom testGroupgroup by UserID2. 取出分组中某列最大/最小值，要求显示其他列要显示表中其他列，用group by 不好实现，可以借助子查询。select * from testGroup awhere ID = (select MAX(ID) from testGroup b where a.UserID = b.UserID)order by ID-或者select * from testGroupwhere ID in (select MAX(ID) from testGroup group by UserID)-或者select * from testGroup as awhere a.ID in (select top 1 ID from testGroup b where a.UserID = b.UserID order by b.OrderID desc)-或者select * from testGroup awhere not exists(select 1 from testGroup b where a.UserID = b.UserID and a.OrderID b.OrderID)-或者select * from testGroup awhere (select count(1) from testGroup b where a.UserID = b.UserID and a.id b.OrderIDhaving count(1) = 2)-或者select * from testGroup awhere (select count(1) from testGroup b where a.UserID = b.UserID and a.ID = b.ID) = b.OrderID) = 3-或者select * from testGroup awhere exists (select 1 from testGroup b where a.UserID = b.UserID and a.OrderID = b.OrderIDhaving count(1) = 3)(2) ROW_NUMBER()select ID, UserID, OrderIDfrom(select *, ROW_NUMBER(