合并多行数据
将多行数据合并
有多行属于同一类别的的记录，可使用
GROUP_CONCAT将其合并
- GROUP_CONCAT
例如这边有是有三个培训相关的表
- MySQL
CREATE TABLE oa_peixun_cost_detail (peixun_id varchar(255) NOT NULL,user_name varchar(255) NOT NULL,PRIMARY KEY (peixun_id,user_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
CREATE TABLE oa_peixun_cost (peixun_id varchar(255) NOT NULL,create_user_name varchar(255) NOT NULL,PRIMARY KEY (peixun_id,create_user_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
CREATE TABLE oa_peixun (peixun_id varchar(255) NOT NULL,create_user_name varchar(255) DEFAULT NULL,PRIMARY KEY (peixun_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
MySQL版本
SELECT peixun_id, GROUP_CONCAT(user_name ORDER BY user_name SEPARATOR ', ') AS users
FROM (SELECT peixun_id, create_user_name AS user_nameFROM oa_peixunUNIONSELECT peixun_id, create_user_name AS user_nameFROM oa_peixun_costUNIONSELECT peixun_id, user_nameFROM oa_peixun_cost_detail
) AS peixun_users
GROUP BY peixun_id
ORDER BY peixun_id;
SQLserver版本
SELECT peixun_id, STRING_AGG(user_name, ', ') AS users
FROM (SELECT peixun_id, create_user_name AS user_nameFROM oa_peixunUNIONSELECT peixun_id, create_user_name AS user_nameFROM oa_peixun_costUNIONSELECT peixun_id, user_nameFROM oa_peixun_cost_detail
) AS peixun_users
GROUP BY peixun_id
ORDER BY peixun_id;
- STRING_AGG，这个函数是SQLSERVER 2017才有的，有2个参数，第1个是要合并的字段、表达式，第2个是用什么分隔符，比如：用逗号作为分隔符。
_AGG，这个函数是SQLSERVER 2017才有的，有2个参数，第1个是要合并的字段、表达式，第2个是用什么分隔符，比如：用逗号作为分隔符。