group by+group_concat解决的小问题

矫情吗；* 2022-11-30 12:33 165阅读 0赞

建测试表：

>     CREATE TABLE test_001
>     (
>       id INT,
>       type2 VARCHAR(10),
>       bizId VARCHAR(10),
>       batchId INT
>     )
>     
>     INSERT INTO test_001(id,type2,bizId,batchId) VALUES(1,1,'uid1',1);
>     INSERT INTO test_001(id,type2,bizId,batchId) VALUES(2,1,'uid2',1);
>     INSERT INTO test_001(id,type2,bizId,batchId) VALUES(3,2,'fid1',1);
>     INSERT INTO test_001(id,type2,bizId,batchId) VALUES(4,2,'fid2',1);
>     INSERT INTO test_001(id,type2,bizId,batchId) VALUES(5,1,'uid3',2);
>     INSERT INTO test_001(id,type2,bizId,batchId) VALUES(6,2,'fid3',3);
>     
>     SELECT * FROM test_001;

第一次解决，KO！

>     SELECT batchId,
>            GROUP_CONCAT(uid) AS uid,
>            GROUP_CONCAT(typeid) AS typeid
>     FROM
>         (SELECT batchId,
>                CASE WHEN type2 = '1' THEN bizId
>                ELSE NULL END AS uid,
>                CASE WHEN type2 = '2' THEN bizId
>                ELSE NULL END AS typeid
>         FROM test_001) g
>     GROUP BY batchId;

![format_png][]

看起来没什么毛病，哈哈哈！继续看看其他高手有没有什么值得学习的解决方案。

>     SELECT * FROM test_001;
>     SELECT batchId,
>            GROUP_CONCAT(CASE WHEN type2 = 1 THEN bizId ELSE NULL END) AS "uid",
>            GROUP_CONCAT(CASE WHEN type2 = 2 THEN bizId ELSE NULL END) AS "typeid"
>     FROM test_001
>     GROUP BY batchId;

不采用子查询显得更加的优雅，get！结果符合要求！

附原文链接：https://www.v2ex.com/t/535679

--------------------

顺手温习一下**concat、concat\_ws、group\_concat**函数的使用方法。

*  **　　concat(str1,str2,str3,...)** 字符串拼接

如果有任何一个参数为null，则返回值为null。

*  **　　concat\_ws(separator,str1,str2,...)**

指定分隔符拼接，分隔符不能为null。

*  　　**group\_concat( )** 分组拼接

group\_concat(\[distinct\] col \[order by xxx desc\] \[separator '分隔符'\])

>     SELECT * FROM test_001
>     
>     -- 例子1
>     SELECT batchId,
>            GROUP_CONCAT(bizId ORDER BY bizId DESC SEPARATOR '_') AS xxx
>     FROM test_001
>     GROUP BY batchId
>     
>     -- 例子2
>     SELECT batchId,
>            GROUP_CONCAT(CONCAT_WS('-',type2,bizId) ORDER BY bizId)
>     FROM test_001
>     GROUP BY batchId

[format_png]: /images/20221123/c6b7e2cf246c43f18a4c7be6cf60538b.png