group by+group_concat解决的小问题

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;

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;

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;

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