如何基于日期列实现Oracle部门统计数据的交叉表查询？

针对你的需求，需要将department_statistics表中的数据按部门分组，把不同日期的早晚样本数总和转换为列展示，以下分两种场景给出实现方案：

场景1：已知固定日期（如示例中的6/12/2024、6/13/2024） #

如果日期是确定的，可以直接用CASE WHEN配合聚合函数实现：

SELECT 
    DEPT_ID,
    -- 6/12/2024的早晚样本总和
    SUM(CASE WHEN DATE = '6/12/2024' THEN SAMPLES_MORNING ELSE 0 END) AS `6/12/2024_sum_samples_morning`,
    SUM(CASE WHEN DATE = '6/12/2024' THEN SAMPLES_EVENING ELSE 0 END) AS `6/12/2024_sum_samples_evening`,
    -- 6/13/2024的早晚样本总和
    SUM(CASE WHEN DATE = '6/13/2024' THEN SAMPLES_MORNING ELSE 0 END) AS `6/13/2024_sum_samples_morning`,
    SUM(CASE WHEN DATE = '6/13/2024' THEN SAMPLES_EVENING ELSE 0 END) AS `6/13/2024_sum_samples_evening`
FROM department_statistics
GROUP BY DEPT_ID
ORDER BY DEPT_ID;

场景2：日期不固定（动态生成列） #

如果表中的日期是动态变化的，需要用动态SQL自动生成对应列：

MySQL 动态SQL实现 #

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'SUM(CASE WHEN DATE = ''', DATE, ''' THEN SAMPLES_MORNING ELSE 0 END) AS `', DATE, '_sum_samples_morning`,',
            'SUM(CASE WHEN DATE = ''', DATE, ''' THEN SAMPLES_EVENING ELSE 0 END) AS `', DATE, '_sum_samples_evening`'
        )
    ) INTO @sql
FROM department_statistics;

SET @sql = CONCAT('SELECT DEPT_ID, ', @sql, ' FROM department_statistics GROUP BY DEPT_ID ORDER BY DEPT_ID');

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

支持PIVOT的数据库（Oracle、SQL Server等） #

可以先将数据纵向展开，再通过PIVOT实现行列转换：

WITH unpivoted_data AS (
    SELECT 
        DEPT_ID,
        DATE || '_sum_samples_morning' AS metric_name,
        SAMPLES_MORNING AS metric_value
    FROM department_statistics
    UNION ALL
    SELECT 
        DEPT_ID,
        DATE || '_sum_samples_evening' AS metric_name,
        SAMPLES_EVENING AS metric_value
    FROM department_statistics
)
SELECT *
FROM unpivoted_data
PIVOT (
    SUM(metric_value)
    FOR metric_name IN (
        '6/12/2024_sum_samples_morning' AS "6/12/2024_sum_samples_morning",
        '6/12/2024_sum_samples_evening' AS "6/12/2024_sum_samples_evening",
        '6/13/2024_sum_samples_morning' AS "6/13/2024_sum_samples_morning",
        '6/13/2024_sum_samples_evening' AS "6/13/2024_sum_samples_evening"
    )
)
ORDER BY DEPT_ID;

注：如果是动态日期，Oracle/SQL Server也需要结合动态SQL生成PIVOT中的列列表。

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