sql分組(orderBy、GroupBy)獲取每組前一(幾)條數據

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 

上面的三種方法執行的結果一致如下圖：