基于SQL动态Pivot实现最近5天奶牛生产数据按日期横向展示

核心逻辑说明 #

• 先筛选出最近5个生产日的所有数据，减少不必要的计算量
• 按奶牛ID、日期分组，聚合得到每头牛每天的总产量
• 通过条件聚合或者数据库内置PIVOT语法，将日期转为横向表头，按从新到旧的顺序排列

静态实现代码（适合临时查询，MySQL语法兼容） #

如果只是单次查询，直接替换代码中最近5天的日期即可，逻辑稳定不易出错

WITH recent_dates AS (
    -- 先获取最近5个生产日
    SELECT DISTINCT Date
    FROM PRODUCTION_TABLE
    ORDER BY Date DESC
    LIMIT 5
),
daily_prod AS (
    -- 聚合每头牛的每日总产量
    SELECT
        Cow_ID,
        Date,
        SUM(Litres) AS daily_total
    FROM PRODUCTION_TABLE
    WHERE Date IN (SELECT Date FROM recent_dates)
    GROUP BY Cow_ID, Date
)
-- 行转列输出，日期按从新到旧排列
SELECT
    Cow_ID,
    MAX(IF(Date = '2024-06-20', daily_total, 0)) AS `2024-06-20`,
    MAX(IF(Date = '2024-06-19', daily_total, 0)) AS `2024-06-19`,
    MAX(IF(Date = '2024-06-18', daily_total, 0)) AS `2024-06-18`,
    MAX(IF(Date = '2024-06-17', daily_total, 0)) AS `2024-06-17`,
    MAX(IF(Date = '2024-06-16', daily_total, 0)) AS `2024-06-16`
FROM daily_prod
GROUP BY Cow_ID
ORDER BY Cow_ID;

动态实现代码（适合定时跑数，自动适配最新5个日期） #

不需要手动修改日期，代码会自动识别最近5个生产日生成表头

-- 拼接动态查询的列字段
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX(IF(Date = ''', Date, ''', daily_total, 0)) AS `', Date, '`')
    ORDER BY Date DESC
) INTO @sql
FROM (
    SELECT DISTINCT Date
    FROM PRODUCTION_TABLE
    ORDER BY Date DESC
    LIMIT 5
) AS recent_dates;

-- 拼接完整查询语句
SET @sql = CONCAT('
WITH daily_prod AS (
    SELECT Cow_ID, Date, SUM(Litres) AS daily_total
    FROM PRODUCTION_TABLE
    WHERE Date IN (SELECT DISTINCT Date FROM PRODUCTION_TABLE ORDER BY Date DESC LIMIT 5)
    GROUP BY Cow_ID, Date
)
SELECT Cow_ID, ', @sql, ' 
FROM daily_prod
GROUP BY Cow_ID
ORDER BY Cow_ID');

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

其他数据库适配说明 #

• PostgreSQL可使用内置crosstab函数实现pivot，核心逻辑和上述方案一致，仅语法细节有差异
• SQL Server可直接使用PIVOT关键字完成行转列，无需手写条件聚合

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