如何统计故障原因出现次数并在同库另一张表生成汇总表？

核心逻辑是按cause字段分组，将同组的downtime值用+拼接，同时统计每组的记录数作为occurence。以下是主流数据库的实现代码：

MySQL #

查询语句 #

SELECT 
  GROUP_CONCAT(downtime SEPARATOR '+') AS downtime,
  cause,
  COUNT(*) AS occurence
FROM 原表名
GROUP BY cause;

插入到新表 #

INSERT INTO 新表名(downtime, cause, occurence)
SELECT 
  GROUP_CONCAT(downtime SEPARATOR '+') AS downtime,
  cause,
  COUNT(*) AS occurence
FROM 原表名
GROUP BY cause;

PostgreSQL #

查询语句 #

SELECT 
  STRING_AGG(downtime, '+' ORDER BY downtime) AS downtime,
  cause,
  COUNT(*) AS occurence
FROM 原表名
GROUP BY cause;

插入到新表 #

INSERT INTO 新表名(downtime, cause, occurence)
SELECT 
  STRING_AGG(downtime, '+' ORDER BY downtime) AS downtime,
  cause,
  COUNT(*) AS occurence
FROM 原表名
GROUP BY cause;

SQL Server #

2017及以上版本（支持STRING_AGG）

SELECT 
  STRING_AGG(downtime, '+') AS downtime,
  cause,
  COUNT(*) AS occurence
FROM 原表名
GROUP BY cause;

旧版本（用STUFF+FOR XML PATH实现拼接）

SELECT 
  STUFF((SELECT '+' + downtime FROM 原表名 t2 WHERE t2.cause = t1.cause FOR XML PATH('')), 1, 1, '') AS downtime,
  cause,
  COUNT(*) AS occurence
FROM 原表名 t1
GROUP BY cause;

插入到新表 #

将上述对应版本的SELECT语句嵌入INSERT即可：

INSERT INTO 新表名(downtime, cause, occurence)
-- 这里放入对应版本的SELECT语句

注意：必须按cause分组，不要误用唯一id字段进行分组或聚合，否则会无法合并同原因的记录。

内容的提问来源于stack exchange，提问作者mariem