SQL按日期统计工单数量及分类维度日期统计可行性咨询

当然可行！这种需求其实就是典型的**行列转置（Pivot）**操作——把日期从行维度转成列维度，同时按工单分类聚合统计数量。针对数千条记录的规模，单条SQL（或结合动态SQL）完全能搞定，下面我结合主流数据库给出具体实现方案：

前提假设 #

假设你的工单表名为work_orders，核心字段包括：

• job_type：工单分类（对应你要的TypesJobs）
• create_date：工单创建日期（格式如2024-05-01）

方案1：静态日期列（适用于已知固定日期范围的场景） #

如果当月日期范围固定（比如你示例中的1-7号），可以直接写死列名，不同数据库写法略有差异：

MySQL/MariaDB（无原生PIVOT，用CASE WHEN实现）

SELECT
    job_type AS TypesJobs,
    COALESCE(SUM(CASE WHEN DAY(create_date) = 1 THEN 1 ELSE 0 END), 0) AS '01',
    COALESCE(SUM(CASE WHEN DAY(create_date) = 2 THEN 1 ELSE 0 END), 0) AS '02',
    COALESCE(SUM(CASE WHEN DAY(create_date) = 3 THEN 1 ELSE 0 END), 0) AS '03',
    COALESCE(SUM(CASE WHEN DAY(create_date) = 4 THEN 1 ELSE 0 END), 0) AS '04',
    COALESCE(SUM(CASE WHEN DAY(create_date) = 5 THEN 1 ELSE 0 END), 0) AS '05',
    COALESCE(SUM(CASE WHEN DAY(create_date) = 6 THEN 1 ELSE 0 END), 0) AS '06',
    COALESCE(SUM(CASE WHEN DAY(create_date) = 7 THEN 1 ELSE 0 END), 0) AS '07'
FROM work_orders
WHERE DATE_FORMAT(create_date, '%Y-%m') = '2024-05' -- 过滤当月数据
GROUP BY job_type
ORDER BY job_type;

注：用COALESCE把空值转成0，避免某天无工单时显示NULL

SQL Server（用原生PIVOT语法）

SELECT
    job_type AS TypesJobs,
    ISNULL([01], 0) AS [01],
    ISNULL([02], 0) AS [02],
    ISNULL([03], 0) AS [03],
    ISNULL([04], 0) AS [04],
    ISNULL([05], 0) AS [05],
    ISNULL([06], 0) AS [06],
    ISNULL([07], 0) AS [07]
FROM (
    SELECT
        job_type,
        FORMAT(create_date, 'dd') AS day_of_month
    FROM work_orders
    WHERE YEAR(create_date) = 2024 AND MONTH(create_date) = 5
) AS src
PIVOT (
    COUNT(day_of_month)
    FOR day_of_month IN ([01], [02], [03], [04], [05], [06], [07])
) AS pvt
ORDER BY job_type;

PostgreSQL（用crosstab函数，需先启用tablefunc扩展）

先启用扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后执行查询：

SELECT
    TypesJobs,
    COALESCE("01", 0) AS "01",
    COALESCE("02", 0) AS "02",
    COALESCE("03", 0) AS "03",
    COALESCE("04", 0) AS "04",
    COALESCE("05", 0) AS "05",
    COALESCE("06", 0) AS "06",
    COALESCE("07", 0) AS "07"
FROM crosstab(
    'SELECT job_type, DAY(create_date), COUNT(*)
     FROM work_orders
     WHERE EXTRACT(YEAR FROM create_date) = 2024 AND EXTRACT(MONTH FROM create_date) = 5
     GROUP BY job_type, DAY(create_date)
     ORDER BY job_type, DAY(create_date)',
    'SELECT generate_series(1,7)' -- 指定日期范围1-7号
) AS ct (TypesJobs text, "01" int, "02" int, "03" int, "04" int, "05" int, "06" int, "07" int)
ORDER BY TypesJobs;

方案2：动态日期列（适用于整月日期不固定的场景，比如28/30/31天） #

如果需要自动适配当月所有日期，不用硬编码列名，可以用动态SQL生成查询语句：

MySQL/MariaDB动态SQL示例

SET @year_month = '2024-05'; -- 指定要统计的年月
SET @sql = NULL;

-- 自动生成所有日期的CASE语句
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'COALESCE(SUM(CASE WHEN DAY(create_date) = ', DAY(create_date), ' THEN 1 ELSE 0 END), 0) AS ''', LPAD(DAY(create_date),2,'0'), ''''
        )
    ) INTO @sql
FROM work_orders
WHERE DATE_FORMAT(create_date, '%Y-%m') = @year_month;

-- 拼接完整SQL
SET @sql = CONCAT(
    'SELECT job_type AS TypesJobs, ', @sql, ' 
     FROM work_orders 
     WHERE DATE_FORMAT(create_date, ''%Y-%m'') = ''', @year_month, ''' 
     GROUP BY job_type 
     ORDER BY job_type'
);

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

SQL Server动态SQL示例

DECLARE @year_month VARCHAR(7) = '2024-05';
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- 生成所有日期的列名
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(FORMAT(create_date, 'dd'))
                      FROM work_orders
                      WHERE FORMAT(create_date, 'yyyy-MM') = @year_month
                      GROUP BY create_date
                      ORDER BY create_date
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 拼接完整SQL
SET @query = 'SELECT job_type AS TypesJobs, ' + @cols + ' 
              FROM (
                    SELECT job_type, FORMAT(create_date, ''dd'') AS day_of_month
                    FROM work_orders
                    WHERE FORMAT(create_date, ''yyyy-MM'') = ''' + @year_month + '''
                   ) AS src
              PIVOT (
                    COUNT(day_of_month)
                    FOR day_of_month IN (' + @cols + ')
                   ) AS pvt
              ORDER BY job_type';

-- 执行动态SQL
EXECUTE sp_executesql @query;

关键注意事项 #

1. 性能优化：数千条记录完全不用担心性能，只要给create_date和job_type建立联合索引（比如CREATE INDEX idx_wo_type_date ON work_orders(job_type, create_date);），查询会非常快。
2. 空值处理：用COALESCE（MySQL/PostgreSQL）或ISNULL（SQL Server）把NULL转成0，避免结果出现空值。
3. 日期过滤灵活性：可以用DATE_TRUNC('month', create_date) = DATE_TRUNC('month', CURRENT_DATE)代替硬编码年月，实现自动统计当前月数据。

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