Oracle SQL：按日期分组统计并将不同CODE值转为列展示的实现方法

针对你的统计需求，我整理了适配不同数据库的SQL实现方案，核心逻辑是先关联两张表并过滤符合条件的数据，再通过行列转换将CODE转为列展示每日计数：

核心逻辑梳理 #

• 关联表A与表B：通过表A.ID = 表B.ID_A建立关联
• 过滤规则：
  • 时间范围限定：time-stamp 介于 2020-11-30 至 2021-02-06
  • 仅统计表B中status IN (0, 1)的记录
• 统计维度：按日期分组，计算每日总记录数+每个CODE的当日记录数
• 格式转换：将CODE的不同取值转为单独列，每行对应一个日期

分数据库实现示例 #

1. SQL Server 版本（内置PIVOT语法） #

SQL Server的PIVOT函数可以直接完成行列转换，代码如下：

WITH filtered_data AS (
    SELECT 
        CAST(b.[time-stamp] AS DATE) AS date,
        a.CODE,
        -- 先计算每日总记录数
        COUNT(*) OVER (PARTITION BY CAST(b.[time-stamp] AS DATE)) AS sum_of_this_day
    FROM 表B b
    JOIN 表A a ON b.ID_A = a.ID
    WHERE 
        b.[time-stamp] BETWEEN '2020-11-30' AND '2021-02-06'
        AND b.status IN (0, 1)
),
code_daily_stats AS (
    SELECT 
        date,
        sum_of_this_day,
        CODE,
        COUNT(CODE) AS code_count
    FROM filtered_data
    GROUP BY date, sum_of_this_day, CODE
)
SELECT 
    date,
    sum_of_this_day,
    -- 替换为实际的67个CODE值，比如[code_1], [code_2], ..., [code_z]
    [code_1], [code_2], [code_3], ..., [code_z]
FROM code_daily_stats
PIVOT (
    MAX(code_count)
    FOR CODE IN ([code_1], [code_2], [code_3], ..., [code_z])
) AS pivot_result
ORDER BY date;

2. MySQL 版本（CASE WHEN实现行列转换） #

MySQL没有内置PIVOT，用CASE WHEN结合聚合函数实现：

SELECT 
    DATE(b.[time-stamp]) AS date,
    COUNT(*) AS sum_of_this_day,
    -- 每个CODE对应一个CASE分支，替换为实际的67个CODE值
    SUM(CASE WHEN a.CODE = 'code_1' THEN 1 ELSE 0 END) AS code_1,
    SUM(CASE WHEN a.CODE = 'code_2' THEN 1 ELSE 0 END) AS code_2,
    SUM(CASE WHEN a.CODE = 'code_3' THEN 1 ELSE 0 END) AS code_3,
    -- ... 依次添加剩余CODE的统计
    SUM(CASE WHEN a.CODE = 'code_z' THEN 1 ELSE 0 END) AS code_z
FROM 表B b
JOIN 表A a ON b.ID_A = a.ID
WHERE 
    b.[time-stamp] BETWEEN '2020-11-30' AND '2021-02-06'
    AND b.status IN (0, 1)
GROUP BY DATE(b.[time-stamp])
ORDER BY date;

3. PostgreSQL 版本（两种实现方式） #

方式一：CASE WHEN（简单直接）

SELECT 
    DATE(b."time-stamp") AS date,
    COUNT(*) AS sum_of_this_day,
    SUM(CASE WHEN a.CODE = 'code_1' THEN 1 ELSE 0 END) AS code_1,
    SUM(CASE WHEN a.CODE = 'code_2' THEN 1 ELSE 0 END) AS code_2,
    SUM(CASE WHEN a.CODE = 'code_3' THEN 1 ELSE 0 END) AS code_3,
    -- ... 依次添加剩余CODE的统计
    SUM(CASE WHEN a.CODE = 'code_z' THEN 1 ELSE 0 END) AS code_z
FROM "表B" b
JOIN "表A" a ON b.ID_A = a.ID
WHERE 
    b."time-stamp" BETWEEN '2020-11-30' AND '2021-02-06'
    AND b.status IN (0, 1)
GROUP BY DATE(b."time-stamp")
ORDER BY date;

方式二：crosstab函数（适合大量CODE场景）

先启用tablefunc扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

再执行统计查询：

-- 先统计每日总记录数
WITH daily_total AS (
    SELECT 
        DATE(b."time-stamp") AS date,
        COUNT(*) AS sum_of_this_day
    FROM "表B" b
    JOIN "表A" a ON b.ID_A = a.ID
    WHERE 
        b."time-stamp" BETWEEN '2020-11-30' AND '2021-02-06'
        AND b.status IN (0, 1)
    GROUP BY DATE(b."time-stamp")
),
code_daily AS (
    SELECT 
        DATE(b."time-stamp") AS date,
        a.CODE,
        COUNT(*) AS code_count
    FROM "表B" b
    JOIN "表A" a ON b.ID_A = a.ID
    WHERE 
        b."time-stamp" BETWEEN '2020-11-30' AND '2021-02-06'
        AND b.status IN (0, 1)
    GROUP BY DATE(b."time-stamp"), a.CODE
)
SELECT 
    dt.date,
    dt.sum_of_this_day,
    -- 替换为实际的67个CODE列
    ct.code_1, ct.code_2, ..., ct.code_z
FROM daily_total dt
JOIN crosstab(
    'SELECT date, CODE, code_count FROM code_daily ORDER BY 1,2',
    'SELECT DISTINCT CODE FROM "表A" ORDER BY CODE'
) AS ct(
    date DATE,
    code_1 INT, code_2 INT, ..., code_z INT
) ON dt.date = ct.date
ORDER BY dt.date;

额外提示 #

• 若CODE数量多（67个），手动编写所有CASE分支繁琐，可以用动态SQL生成语句（比如MySQL存储过程、SQL Server动态拼接）。
• 日期转换时用CAST或DATE函数确保按纯日期分组，避免时间部分干扰统计。
• 若某日期下某CODE无记录，结果会显示NULL，可以用COALESCE函数转为0。

内容的提问来源于stack exchange，提问作者Jacek Krzyżanowski