将两个字段中的值合并到一个字段中

 

 

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 将多个值进行合并