SELECT
GROUP_CONCAT(b.name) AS 'name'
FROM (
SELECT CONCAT(a.jobs,':',a.name) AS 'name',a.data_id AS 'data_id' FROM (
SELECT
CASE bp.jobs WHEN 1 THEN '董事'
WHEN 2 THEN '监事'
WHEN 3 THEN '董事会观察员'
END AS 'jobs' ,
GROUP_CONCAT(bp.name) AS 'name',
bp.data_id AS 'data_id'
FROM
bus_delegating bp
WHERE
bp.data_id='a4d3d9472c404975b12f6f9a90a86a25'
GROUP BY bp.jobs) a
)b
首先使用GROUP_CONCAT将分组出来的多个值合并在一起
例：
SELECT
CASE bp.jobs WHEN 1 THEN '董事'
WHEN 2 THEN '监事'
WHEN 3 THEN '董事会观察员'
END AS 'jobs' ,
GROUP_CONCAT(bp.name) AS 'name',
bp.data_id AS 'data_id'
FROM
bus_delegating bp
WHERE
bp.data_id='a4d3d9472c404975b12f6f9a90a86a25'
GROUP BY bp.jobs
然后使用 CONCAT 合并 jobs和name
SELECT CONCAT(a.jobs,':',a.name) AS 'name',a.data_id AS 'data_id' FROM (
SELECT
CASE bp.jobs WHEN 1 THEN '董事'
WHEN 2 THEN '监事'
WHEN 3 THEN '董事会观察员'
END AS 'jobs' ,
GROUP_CONCAT(bp.name) AS 'name',
bp.data_id AS 'data_id'
FROM
bus_delegating bp
WHERE
bp.data_id='a4d3d9472c404975b12f6f9a90a86a25'
GROUP BY bp.jobs) a
最后再使用 GROUP_CONCAT 将多个值进行合并
