SQL需求：多年度每日记录统计及跨年度同期对比

核心需求是把tblRegister表中每日的记录，按月-日维度分组，统计各年度对应日期的记录数，输出以日期为行、各年度为列的结果，方便后续做趋势分析。以下是不同数据库的实现方案：

1. MySQL（无原生PIVOT，用条件聚合） #

如果你的数据库是MySQL，直接用条件聚合就能实现，静态写法适合已知年份的场景：

SELECT
    DATE_FORMAT(register_date, '%m-%d') AS 日期,
    COUNT(CASE WHEN YEAR(register_date) = 2021 THEN id END) AS `2021年记录数`,
    COUNT(CASE WHEN YEAR(register_date) = 2022 THEN id END) AS `2022年记录数`,
    COUNT(CASE WHEN YEAR(register_date) = 2023 THEN id END) AS `2023年记录数`,
    COUNT(CASE WHEN YEAR(register_date) = 2024 THEN id END) AS `2024年记录数`
FROM tblRegister
GROUP BY DATE_FORMAT(register_date, '%m-%d')
ORDER BY DATE_FORMAT(register_date, '%m-%d');

如果年份不固定，用动态SQL自动生成列：

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'COUNT(CASE WHEN YEAR(register_date) = ', YEAR(register_date), ' THEN id END) AS `', YEAR(register_date), '年记录数`'
        )
    ) INTO @sql
FROM tblRegister;

SET @sql = CONCAT('SELECT DATE_FORMAT(register_date, ''%m-%d'') AS 日期, ', @sql, ' FROM tblRegister GROUP BY DATE_FORMAT(register_date, ''%m-%d'') ORDER BY DATE_FORMAT(register_date, ''%m-%d'')');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

2. SQL Server（用PIVOT关键字） #

SQL Server有原生的PIVOT语法，写法更简洁：

SELECT
    日期,
    [2021] AS [2021年记录数],
    [2022] AS [2022年记录数],
    [2023] AS [2023年记录数],
    [2024] AS [2024年记录数]
FROM (
    SELECT
        FORMAT(register_date, 'MM-dd') AS 日期,
        YEAR(register_date) AS 年度,
        id
    FROM tblRegister
) AS SourceTable
PIVOT (
    COUNT(id)
    FOR 年度 IN ([2021], [2022], [2023], [2024])
) AS PivotTable
ORDER BY 日期;

3. PostgreSQL（用crosstab函数） #

PostgreSQL需要先启用tablefunc扩展，再用交叉表函数实现：
首先启用扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后执行查询：

SELECT * FROM crosstab(
    'SELECT
         TO_CHAR(register_date, ''MM-dd'') AS 日期,
         YEAR(register_date) AS 年度,
         COUNT(id) AS 记录数
     FROM tblRegister
     GROUP BY TO_CHAR(register_date, ''MM-dd''), YEAR(register_date)
     ORDER BY 1, 2',
    'SELECT DISTINCT YEAR(register_date) FROM tblRegister ORDER BY 1'
) AS ct(
    日期 TEXT,
    "2021年记录数" INT,
    "2022年记录数" INT,
    "2023年记录数" INT,
    "2024年记录数" INT
)
ORDER BY 日期;

注意事项 #

• 替换代码中的register_date为你表中实际存储日期的字段名
• 如果有更多年度，只需在对应位置添加年份相关的列即可
• 结果会按月-日排序，方便后续绘图做趋势对比

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