编写SQL查询实现员工日期记录行转列及总数统计

数据库参考数据 #

预期结果 #

通用SQL解决方案（适用于MySQL、PostgreSQL等多数数据库） #

使用CASE WHEN实现行转列统计，逻辑清晰且兼容性强：

SELECT
    emp_id,
    COUNT(CASE WHEN date = '08-09-22' THEN 1 END) AS `08-09-22`,
    COUNT(CASE WHEN date = '09-09-22' THEN 1 END) AS `09-09-22`,
    COUNT(CASE WHEN date = '10-09-22' THEN 1 END) AS `10-09-22`,
    COUNT(*) AS total
FROM
    your_table_name -- 替换为你的实际表名
GROUP BY
    emp_id
ORDER BY
    emp_id;

逻辑说明 #

1. CASE WHEN针对每个日期做判断，符合条件时返回1，否则返回NULL；COUNT函数会自动忽略NULL值，从而统计出员工在对应日期的出现次数。
2. COUNT(*)直接统计每个员工的总记录数，得到total列。
3. 按emp_id分组并排序，最终得到预期的结构化结果。

SQL Server专属解决方案（使用PIVOT语法） #

如果使用SQL Server，也可以用内置的PIVOT函数实现：

SELECT
    emp_id,
    ISNULL([08-09-22], 0) AS [08-09-22],
    ISNULL([09-09-22], 0) AS [09-09-22],
    ISNULL([10-09-22], 0) AS [10-09-22],
    total
FROM (
    SELECT
        emp_id,
        date,
        COUNT(*) OVER (PARTITION BY emp_id) AS total
    FROM your_table_name -- 替换为你的实际表名
) AS src
PIVOT (
    COUNT(date)
    FOR date IN ([08-09-22], [09-09-22], [10-09-22])
) AS pvt
ORDER BY emp_id;

逻辑说明 #

1. 子查询先通过窗口函数COUNT(*) OVER (PARTITION BY emp_id)计算每个员工的总次数。
2. PIVOT将date列的取值转为列名，统计对应日期的出现次数。
3. ISNULL将无记录的日期值转为0，匹配预期结果的格式。

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