sqlserver 查询时合并相同项

一、例子

现在有一个临时表如下：

autoID	id	name	description	time
1	32167542	张三	电脑	2021-12-1
4	48978925	李四	手机	2021-12-5
7	32167542	张三	A	2021-12-8
11	48978925	李四	B	2021-12-15

查询时需要合并id和name相同的description，合并后数据取autoID最小的记录,即，效果如下：

autoID	id	name	description	time
1	32167542	张三	电脑A	2021-12-1
4	48978925	李四	手机B	2021-12-5

二、建表

select * into #table
from (
	select 1 as autoID, '32167542' as id, '张三' as name, '电脑' as description, 2021-12-1 as time
	union
	select 4 as autoID, '48978925' as id, '李四' as name, '手机' as description, 2021-12-5 as time
	union
	select 7 as autoID, '32167542' as id, '张三' as name, 'A' as description, 2021-12-8 as time
	union
	select 11 as autoID, '48978925' as id, '李四' as name, 'B' as description, 2021-12-15 as time
)x

三、过程

1. 根据id和name分组查询，使用for xml path合并description并将数据放入临时表中

IF OBJECT_ID('Tempdb.dbo.#tableDescription') IS NOT NULL DROP TABLE #tableDescription
select t1.id, t1.name
	,stuff((select '/' + description from #table t2
where t1.id = t2.id
	and t1.name = t2.name
	for xml path('')),1,1,'') as description  
into #tableDescription
from #table t1  
group by t1.id, t1.name

2. 修改临时表#table的Description

update #table.description = #tableDescription.description
from #table
left join #tableDescription
on #table.id = #tableDescription.id
where #tableDescription.id is not null

3. 根据id和name分组，取每组最前面的一行数据

IF OBJECT_ID('Tempdb.dbo.#autoMin') IS NOT NULL DROP TABLE #autoMin
select min(autoID) as autoID into #autoMin from #table group by id,name

4. 联表，筛选出每组第一行的数据

select #table.* from #table
left join #autoMin on #table.autoID = #autoMin.autoID
where #autoMin.autoID is not null