求助：基于日期转置文件统计表格的SQL实现（含Q1查询）

原Query Q1用于获取2022年10月1日至30日期间，各国家每日接收的文件总数。要将结果转置为国家为行、日期为列的表格，可根据使用的数据库类型选择以下方案：

方案1：使用SQL Server的PIVOT关键字 #

静态列写法（适合日期范围固定且数量少的场景） #

SELECT Country, 
       [2022-10-01], [2022-10-02], [2022-10-03],
       -- 依次补充10月4日至29日的列名，最后添加[2022-10-30]
       [2022-10-30]
FROM (
    SELECT 
        b.country,
        CAST(a.ProcessDate AS DATE) AS DATE,
        COUNT(a.ProcessDate) AS total
    FROM Log a
    LEFT JOIN Files b ON a.FileID = b.FileID
    WHERE a.ProcessDate BETWEEN '2022-10-01' AND '2022-10-30'
    GROUP BY b.Country, CAST(a.ProcessDate AS DATE)
) AS SourceTable
PIVOT (
    SUM(total)
    FOR DATE IN ([2022-10-01], [2022-10-02], [2022-10-30])
) AS PivotTable;

动态列写法（适合日期范围大、无需手动列日期的场景） #

通过动态SQL自动生成日期列，避免手动维护：

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

-- 生成所有目标日期的格式化列名
SET @cols = STUFF((SELECT ',' + QUOTENAME(CAST(ProcessDate AS DATE)) 
                   FROM Log
                   WHERE ProcessDate BETWEEN '2022-10-01' AND '2022-10-30'
                   GROUP BY CAST(ProcessDate AS DATE)
                   ORDER BY CAST(ProcessDate AS DATE)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

-- 拼接完整的PIVOT查询语句
SET @query = 'SELECT Country, ' + @cols + ' 
              FROM (
                    SELECT 
                        b.country,
                        CAST(a.ProcessDate AS DATE) AS DATE,
                        COUNT(a.ProcessDate) AS total
                    FROM Log a
                    LEFT JOIN Files b ON a.FileID = b.FileID
                    WHERE a.ProcessDate BETWEEN ''2022-10-01'' AND ''2022-10-30''
                    GROUP BY b.Country, CAST(a.ProcessDate AS DATE)
                ) AS SourceTable
                PIVOT (
                    SUM(total)
                    FOR DATE IN (' + @cols + ')
                ) AS PivotTable';

-- 执行动态SQL
EXECUTE(@query);

方案2：通用条件聚合（适配MySQL、PostgreSQL等无PIVOT关键字的数据库） #

通过CASE语句实现转置，兼容性更强：

SELECT 
    b.country,
    COALESCE(COUNT(CASE WHEN CAST(a.ProcessDate AS DATE) = '2022-10-01' THEN a.ProcessDate END), 0) AS `2022-10-01`,
    COALESCE(COUNT(CASE WHEN CAST(a.ProcessDate AS DATE) = '2022-10-02' THEN a.ProcessDate END), 0) AS `2022-10-02`,
    -- 依次补充10月4日至29日的CASE语句
    COALESCE(COUNT(CASE WHEN CAST(a.ProcessDate AS DATE) = '2022-10-30' THEN a.ProcessDate END), 0) AS `2022-10-30`
FROM Log a
LEFT JOIN Files b ON a.FileID = b.FileID
WHERE a.ProcessDate BETWEEN '2022-10-01' AND '2022-10-30'
GROUP BY b.Country;

注意事项 #

• 日期列名需用反引号（MySQL）或方括号（SQL Server）包裹，避免语法错误；
• 使用COALESCE可将无数据的日期列值从NULL转为0，更符合统计展示需求。

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