合并多行数据

将多行数据合并

有多行属于同一类别的的记录，可使用 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_name
    FROM oa_peixun
    UNION
    SELECT peixun_id, create_user_name AS user_name
    FROM oa_peixun_cost
    UNION
    SELECT peixun_id, user_name
    FROM 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_name
    FROM oa_peixun
    UNION
    SELECT peixun_id, create_user_name AS user_name
    FROM oa_peixun_cost
    UNION
    SELECT peixun_id, user_name
    FROM 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个是用什么分隔符，比如：用逗号作为分隔符。