SQL Server分组连接并去重类似Mysql的Group_Concat功能实现

快来打我* 2023-10-04 11:03 0阅读 0赞

-- 建表
    CREATE TABLE TestA(id int, name varchar(10));
    -- 插入测试数据
    INSERT INTO TestA VALUES(1, 'aa');
    INSERT INTO TestA VALUES(1, 'bb');
    INSERT INTO TestA VALUES(1, 'bb');
    INSERT INTO TestA VALUES(1, 'eeac');
    INSERT INTO TestA VALUES(2, 'cc');
    INSERT INTO TestA VALUES(2, 'dd');
    INSERT INTO TestA VALUES(2, 'dd');
    INSERT INTO TestA VALUES(2, 'ff33');
    INSERT INTO TestA VALUES(3, '111');
    INSERT INTO TestA VALUES(3, '222');
    --测试查询
    select * from TestA
    -- FOR XML PATH用法，行转列，将某一字段的所有行值连接为一个字段
    SELECT '[ '+name+' ]' FROM TestA FOR XML PATH('')
    --分组连接方法一：
    SELECT distinct(id),name = stuff((SELECT ',' + name FROM TestA t WHERE t.id = TestA.id FOR xml path('')) , 1 , 1 , '') FROM TestA 
    --分组连接方法二：
    --里面一层查出单独的name，拼成一行，然后使用where条件连接外层,最终发现后面会多一个,号
    SELECT id, (SELECT name+',' FROM TestA  WHERE id=a.id  FOR XML PATH('')) AS names FROM TestA a  GROUP BY id
    -- 用Left去掉最后那个空格
    SELECT a.id, Left(names,len(names)-1) as names from (SELECT id, (SELECT name+',' FROM TestA  WHERE id=a.id  FOR XML PATH('')) AS names FROM TestA a  GROUP BY id) a
    
    --实现分组行转列连接并去重,stuff
    SELECT distinct(id),name = stuff((SELECT ',' + name FROM TestA t  WHERE t.id = TestA.id GROUP BY name FOR xml path('')) , 1 , 1 , '') FROM TestA 
    --实现分组行转列连接并去重,left
    SELECT a.id, Left(names,len(names)-1) as names from (SELECT id,  (SELECT name+',' FROM TestA  WHERE id=a.id GROUP BY name FOR XML PATH('')) AS names FROM TestA a  GROUP BY id) a
    
    --当然这个去重也可以采用distinct来实现
    SELECT distinct(id),name = stuff((SELECT distinct ',' + name FROM TestA t  WHERE t.id = TestA.id FOR xml path('')) , 1 , 1 , '') FROM TestA

效果：

正常查询：

![20210417002431415.png][]

分组连接成一个字段

![20210417002506122.png][]

分组连接并去重

![20210417002533572.png][]

[20210417002431415.png]: https://img-blog.csdnimg.cn/20210417002431415.png
[20210417002506122.png]: https://img-blog.csdnimg.cn/20210417002506122.png
[20210417002533572.png]: https://img-blog.csdnimg.cn/20210417002533572.png