当前位置：首页 > news >正文

sql分组(orderBy、GroupBy)获取每组前一(几)条数据

news 来源：原创 2024/5/22 6:09:54

sql数据库实现分组并取每组的前1(几)条数据

测试数据准备工作：

根据某一个字段分组取最大(小)值所在行的数据：

创建表并且插入数据

CREATE table Test_orderByOrGroupBy_tb(Name nvarchar(50),Val int,Describe nvarchar(50)) 
insert into Test_orderByOrGroupBy_tb values('a', 1, 'a1--a的第一个值') 
insert into Test_orderByOrGroupBy_tb values('b', 2, 'b2b2b2b2b2b2b2b2b值') 
insert into Test_orderByOrGroupBy_tb values('a', 2, 'a2(a的第二个值)') 
insert into Test_orderByOrGroupBy_tb values('b', 1, 'b1--b的第一个值') 
insert into Test_orderByOrGroupBy_tb values('a', 3, 'a3:a的第三个值') 
insert into Test_orderByOrGroupBy_tb values('b', 3, 'b3:b的第三个值') 
insert into Test_orderByOrGroupBy_tb values('c', 1, 'c1c1c1c1c1c1c1c1c1c1c值')
insert into Test_orderByOrGroupBy_tb values('b', 5, 'b5b5b5b5b5b5b5b5b5b5值') 
insert into Test_orderByOrGroupBy_tb values('c', 2, 'c2c2c2c2c2c2c2c2c2c2值') 
insert into Test_orderByOrGroupBy_tb values('b', 4, 'b4b4b4b4b4b4b4b4b值') 
GO

1、根据Name分组取Val最大的值所在行的数据。

Sql语句代码如下：

--方法1：
select a.* from Test_orderByOrGroupBy_tb a where Val = (select max(Val) from Test_orderByOrGroupBy_tb where Name = a.Name) order by a.Name
--方法2： 
select a.* from Test_orderByOrGroupBy_tb a,(select Name,max(Val) Val from Test_orderByOrGroupBy_tb group by Name) b where a.Name = b.Name and a.Val = b.Val order by a.Name 
--方法3： 
select a.* from Test_orderByOrGroupBy_tb a inner join (select Name,max(Val) Val from Test_orderByOrGroupBy_tb group by Name) b on a.Name = b.Name and a.Val = b.Val order by a.Name 
--方法4: 
select a.* from Test_orderByOrGroupBy_tb a where 1 > (select count(*) from Test_orderByOrGroupBy_tb where Name = a.Name and Val > a.Val ) order by a.Name --其中1表示获取分组中前一条数据
--方法5： 
select a.* from Test_orderByOrGroupBy_tb a where not exists(select 1 from Test_orderByOrGroupBy_tb where Name = a.Name and Val > a.Val)

上面的5种方法的sql执行执行结果一样，结果如下图：

2、根据Name分组取Val最小的值所在行的数据。

--方法1：
select a.* from Test_orderByOrGroupBy_tb a where Val = (select min(Val) from Test_orderByOrGroupBy_tb where Name = a.Name) order by a.Name
--方法2： 
select a.* from Test_orderByOrGroupBy_tb a,(select Name,min(Val) Val from Test_orderByOrGroupBy_tb group by Name) b where a.Name = b.Name and a.Val = b.Val order by a.Name 
--方法3： 
select a.* from Test_orderByOrGroupBy_tb a inner join (select Name,min(Val) Val from Test_orderByOrGroupBy_tb group by Name) b on a.Name = b.Name and a.Val = b.Val order by a.Name 
--方法4: 
select a.* from Test_orderByOrGroupBy_tb a where 1 > (select count(*) from Test_orderByOrGroupBy_tb where Name = a.Name and Val < a.Val ) order by a.Name --其中1表示获取分组中前一条数据
--方法5： 
select a.* from Test_orderByOrGroupBy_tb a where not exists(select 1 from Test_orderByOrGroupBy_tb where Name = a.Name and Val < a.Val)

上面5种方法执行结果是一样的，如下图：

3、根据Name分组取第一次出现的行所在的数据。

select a.* from Test_orderByOrGroupBy_tb a where a.Val = (select top 1 val from Test_orderByOrGroupBy_tb where a.Name = a.Name) order by a.Name

执行结果如下图：

4、根据Name分组随机取一条数据

select a.* from Test_orderByOrGroupBy_tb a where a.Val = (select top 1 Val from Test_orderByOrGroupBy_tb where Name = a.Name order by newid()) order by a.Name

运行几次执行结果如下图：

5、根据Name分组取最大的两个(N个)Val

--方法一：
select a.* from Test_orderByOrGroupBy_tb a where 2 > (select count(*) from Test_orderByOrGroupBy_tb where Name = a.Name and Val > a.Val ) order by a.Name,a.Val 
--方法二：
select a.* from Test_orderByOrGroupBy_tb a where val in (select top 2 val from Test_orderByOrGroupBy_tb where Name=a.Name order by Val desc) order by a.Name,a.Val 
--方法三：
SELECT a.* from Test_orderByOrGroupBy_tb a where exists (select count(*) from Test_orderByOrGroupBy_tb where Name = a.Name and Val > a.Val having Count(*) < 2) order by a.Name

上面的三种方法执行结果是一致的如下图：

6、根据Name分组取最小的两个(N个)Val

--方法一：
select a.* from Test_orderByOrGroupBy_tb a where 2 > (select count(*) from Test_orderByOrGroupBy_tb where Name = a.Name and val < a.val ) order by a.name,a.val 
--方法二：
select a.* from Test_orderByOrGroupBy_tb a where val in (select top 2 val from Test_orderByOrGroupBy_tb where name=a.name order by val) order by a.name,a.val 
--方法三
SELECT a.* from Test_orderByOrGroupBy_tb a where exists (select count(*) from Test_orderByOrGroupBy_tb where Name = a.Name and val < a.val having Count(*) < 2) order by a.name