(sql server)sql 分组取top1

xiaoshuai1992 2021-08-02 原文

经常遇到这样的问题，相同ID的数据有多笔，但是只能任取其中一笔，下面是我的一个思考过程和学习过程。虽然比较基础和简单，但是总会有人不知道，也可以学习一下。

1：建表和初始化数据

create table TestTop
(
ID nvarchar(10), 
Name nvarchar(10),
Name_en nvarchar(10),
NoOne char(5),
NoTwo char(5),
NoThree char(5)
)
insert into TestTop values(\'I00001\',N\'测试1\',\'abc\',\'00001\',\'00002\',\'00003\')
insert into TestTop values(\'I00001\',N\'测试1\',\'abc\',\'00001\',\'00002\',\'00003\')
insert into TestTop values(\'I00001\',N\'测试2\',\'bca\',\'00002\',\'00002\',\'00003\')
insert into TestTop values(\'I00001\',N\'测试3\',\'aaa\',\'00003\',\'00001\',\'00003\')

insert into TestTop values(\'I00002\',N\'2测试1\',\'gfg\',\'00001\',\'05002\',\'09003\')
insert into TestTop values(\'I00002\',N\'2测试2\',\'dd\',\'0022\',\'00002\',\'00003\')

2：首先想到的肯定是group by ，但是group by 后面的栏位必须是聚合函数，如果用max或min，每个栏位取都不一定是同一笔数据的信息，这样就有错误

select * from TestTop
select ID,max(Name)Name,max(Name_en)Name_en,max(NoOne)NoOne,max(NoTwo)NoTwo,max(NoThree)NoThree
from TestTop group by ID

3：本着先思考再找答案的思想，我想了一下，想到了一个很笨的办法，如果我每个ID都排序取top1,那么top1的数据是固定的，这样可以取到完整的一条，这样是可以实现的。

但是还是有一个问题，如果有2条数据一模一样，那这样还要distinct一下。这个方法有个bug，如果有栏位为null,等于的时候就会出问题。

select * from TestTop
select distinct ID,Name,Name_en,NoOne,NoTwo,NoThree from TestTop TT
where TT.Name=(select top 1 Name from TestTop where ID=TT.ID order by ID,Name,Name_en,NoOne,NoTwo,NoThree asc)
and TT.Name_en=(select top 1 Name_en from TestTop where ID=TT.ID order by ID,Name,Name_en,NoOne,NoTwo,NoThree asc)
and TT.NoOne=(select top 1 NoOne from TestTop where ID=TT.ID order by ID,Name,Name_en,NoOne,NoTwo,NoThree asc)
and TT.NoTwo=(select top 1 NoTwo from TestTop where ID=TT.ID order by ID,Name,Name_en,NoOne,NoTwo,NoThree asc)
and TT.NoThree=(select top 1 NoThree from TestTop where ID=TT.ID order by ID,Name,Name_en,NoOne,NoTwo,NoThree asc)

4：刚那个方法虽然可以实现，但是如果栏位很多，那要写的where条件就太多了，这时我就在网上查询了一下，发现了一个好用的办法。

row_number 方法配合partition by 分组编号，再取编号为1的就可以实现了。

partition可以分区，为每个ID编号，ID切换后重新开始编号，这个方法以前还没见过，算是学习了。

select * from TestTop
select ROW_NUMBER() over(partition by ID order by ID) pid, * into #temp from TestTop
select * from #temp
select ID,Name,Name_en,NoOne,NoTwo,NoThree from #temp where pid=1
drop table #temp