group by+group_concat解决的小问题-蒲公英云

group by+group_concat解决的小问题

矫情吗；* 2022-11-30 12:33 244阅读 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