如何用SQL将单列日期转换为多列月份统计登录次数？

原数据 #

目标结果 #

解决方案 #

方法1：通用CASE WHEN分组统计 #

这种方法适配所有支持标准SQL的数据库，兼容性最强：

SELECT
    Name,
    COUNT(CASE WHEN DATE_FORMAT(LoginDate, '%Y-%m') = '2020-01' THEN 1 END) AS Jan,
    COUNT(CASE WHEN DATE_FORMAT(LoginDate, '%Y-%m') = '2020-02' THEN 1 END) AS Feb
FROM
    your_table_name
GROUP BY
    Name;

不同数据库的日期格式化函数有差异，替换对应函数即可：

• MySQL/MariaDB：DATE_FORMAT(LoginDate, '%Y-%m')
• SQL Server：CONVERT(varchar(7), LoginDate, 120)
• Oracle：TO_CHAR(LoginDate, 'YYYY-MM')
• PostgreSQL：TO_CHAR(LoginDate, 'YYYY-MM')

方法2：使用PIVOT函数（部分数据库支持） #

如果你的数据库支持PIVOT语法（如SQL Server、Oracle、PostgreSQL 11+），可以用更简洁的写法：

SQL Server版本

SELECT
    Name,
    ISNULL([2020-01], 0) AS Jan,
    ISNULL([2020-02], 0) AS Feb
FROM (
    SELECT
        Name,
        CONVERT(varchar(7), LoginDate, 120) AS Month
    FROM your_table_name
) AS SourceTable
PIVOT (
    COUNT(Month)
    FOR Month IN ([2020-01], [2020-02])
) AS PivotTable;

Oracle版本

SELECT
    Name,
    NVL(Jan, 0) AS Jan,
    NVL(Feb, 0) AS Feb
FROM (
    SELECT
        Name,
        TO_CHAR(LoginDate, 'Mon') AS Month,
        1 AS cnt
    FROM your_table_name
)
PIVOT (
    COUNT(cnt)
    FOR Month IN ('Jan' AS Jan, 'Feb' AS Feb)
);

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