如何编写SQL查询按Remarks分组统计各Status值的数量

我有master和Details两张表，需要生成一份报表，按remarks分组统计每个status值的出现次数。

表结构 #

Table: master #

m_id (Primary Key)
remarks (TEXT)

Table: Details #

d_id
m_id (foreign key referencing master.m_id)
item
status

示例数据 #

-- Inserting into master table
INSERT INTO master(m_id, remarks) VALUES(1, 'Remarks1');
INSERT INTO master(m_id, remarks) VALUES(2, 'Remarks2');

-- Inserting into Details table
INSERT INTO Details(d_id, m_id, item, status) VALUES(1, 1, 'Item1', 1);
INSERT INTO Details(d_id, m_id, item, status) VALUES(2, 1, 'Item2', 2);
INSERT INTO Details(d_id, m_id, item, status) VALUES(3, 1, 'Item3', 2);
INSERT INTO Details(d_id, m_id, item, status) VALUES(4, 1, 'Item3', 3);

INSERT INTO Details(d_id, m_id, item, status) VALUES(5, 2, 'Item1', 3);
INSERT INTO Details(d_id, m_id, item, status) VALUES(6, 2, 'Item2', 3);
INSERT INTO Details(d_id, m_id, item, status) VALUES(7, 2, 'Item3', 2);

期望输出 #

我尝试过使用CASE语句，但存在列名动态的问题，寻求可行的解决方案。

一、静态列场景（已知所有status值） #

如果status的可能值是固定的（比如示例中的1、2、3），直接用CASE配合SUM就能实现需求，不会有问题：

SELECT
    m.remarks,
    SUM(CASE WHEN d.status = 1 THEN 1 ELSE 0 END) AS Status1_count,
    SUM(CASE WHEN d.status = 2 THEN 1 ELSE 0 END) AS Status2_count,
    SUM(CASE WHEN d.status = 3 THEN 1 ELSE 0 END) AS Status3_count
FROM master m
LEFT JOIN Details d ON m.m_id = d.m_id
GROUP BY m.remarks
ORDER BY m.remarks;

这里用LEFT JOIN保证即使某个remarks没有对应Details数据，也会显示0值，符合期望输出。

二、动态列场景（status值未知或可变） #

如果status的取值是动态变化的，无法提前写死CASE语句，需要用动态SQL来生成查询语句，不同数据库的实现方式略有不同：

1. MySQL/MariaDB 实现 #

SET @sql = NULL;

-- 动态生成所有status对应的列
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'SUM(CASE WHEN d.status = ', status, ' THEN 1 ELSE 0 END) AS Status', status, '_count'
    )
) INTO @sql
FROM Details;

-- 拼接完整SQL语句
SET @sql = CONCAT(
    'SELECT m.remarks, ', @sql, ' 
     FROM master m
     LEFT JOIN Details d ON m.m_id = d.m_id
     GROUP BY m.remarks
     ORDER BY m.remarks;'
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

2. PostgreSQL 实现 #

DO $$
DECLARE
    cols TEXT;
BEGIN
    -- 动态生成列定义
    SELECT string_agg(
        DISTINCT format(
            'SUM(CASE WHEN d.status = %L THEN 1 ELSE 0 END) AS "Status%s_count"',
            status, status
        ),
        ', '
    ) INTO cols
    FROM Details;

    -- 执行动态查询
    EXECUTE format(
        'SELECT m.remarks, %s
         FROM master m
         LEFT JOIN Details d ON m.m_id = d.m_id
         GROUP BY m.remarks
         ORDER BY m.remarks;',
        cols
    );
END $$;

3. SQL Server 实现 #

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 生成列列表
SELECT @cols = STRING_AGG(
    DISTINCT CONCAT(
        'SUM(CASE WHEN d.status = ', status, ' THEN 1 ELSE 0 END) AS Status', status, '_count'
    ),
    ', '
)
FROM Details;

-- 拼接并执行SQL
SET @sql = CONCAT(
    'SELECT m.remarks, ', @cols, ' 
     FROM master m
     LEFT JOIN Details d ON m.m_id = d.m_id
     GROUP BY m.remarks
     ORDER BY m.remarks;'
);

EXEC sp_executesql @sql;

动态SQL的核心逻辑是先从Details表中获取所有唯一的status值，自动生成对应的CASE统计列，再拼接完整的查询语句执行，实现列的动态生成。

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