如何在MySQL中通过单查询统计文件的测试状态与运行次数？

现有表结构 #

file表 #

CREATE TABLE `file` (
  `id` int(11) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `is_base_found` tinyint(1) NOT NULL DEFAULT 0,
  `is_target_found` tinyint(1) NOT NULL DEFAULT 0,
  `last_run_date` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

statistics表 #

CREATE TABLE `statistics` (
  `id` int(11) NOT NULL,
  `file_id` int(11) NOT NULL,
  `status` varchar(8) NOT NULL utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注：statistics表的file_id字段关联file表的id字段

statistics表示例数据 #

需求 #

通过单个MySQL查询获取每个文件的以下信息：

• 文件名（File Name）
• passed状态计数（# Passed）
• failed状态计数（# Failed）
• 总运行次数（# Run，即该文件在statistics表中的记录总数）

期望输出格式：

实现方案 #

使用LEFT JOIN关联两张表，结合SUM(CASE...)统计不同状态的数量，COUNT()统计总运行次数，确保没有统计记录的文件也能显示0值：

SELECT
    f.file_name AS `File Name`,
    SUM(CASE WHEN s.status = 'passed' THEN 1 ELSE 0 END) AS `# Passed`,
    SUM(CASE WHEN s.status = 'failed' THEN 1 ELSE 0 END) AS `# Failed`,
    COUNT(s.id) AS `# Run`
FROM
    file f
LEFT JOIN
    statistics s ON f.id = s.file_id
GROUP BY
    f.id, f.file_name
ORDER BY
    f.file_name;

说明 #

1. LEFT JOIN：确保即使某个文件在statistics表中没有记录，也会被纳入结果，此时计数均为0
2. SUM(CASE...)：通过条件判断分别统计passed和failed状态的记录数
3. COUNT(s.id)：统计该文件的总运行次数（仅统计statistics表中存在的记录，因为LEFT JOIN中无匹配时s.id为NULL，不会被计数）
4. GROUP BY：按文件的id和file_name分组，确保每个文件只返回一条统计结果

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