group by+group_concat解决的小问题 矫情吗;* 2022-11-30 12:33 123阅读 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
还没有评论,来说两句吧...