如何修改Oracle查询以展示最近6天的仓库活动统计数据？

错误根源分析 #

你碰到的几个错误核心问题在于子查询的关联逻辑或列引用不当：

• ORA-00904：子查询中使用的列名不存在，或别名未被主查询正确识别
• ORA-00936：CASE WHEN语句缺少必要分支，表达式不完整
• ORA-01790：子查询返回列的数据类型与主查询对应列不匹配

正确实现方案 #

放弃子查询嵌套，改用行转列聚合的标准写法，能直接规避上述所有错误：

SELECT
    facility_activity AS "Facility Activity",
    -- 当日统计
    SUM(CASE WHEN TRUNC(activity_date) = TRUNC(SYSDATE) THEN activity_count ELSE 0 END) AS "Today",
    -- 昨日统计
    SUM(CASE WHEN TRUNC(activity_date) = TRUNC(SYSDATE) - 1 THEN activity_count ELSE 0 END) AS "Yesterday",
    -- 2天前统计
    SUM(CASE WHEN TRUNC(activity_date) = TRUNC(SYSDATE) - 2 THEN activity_count ELSE 0 END) AS "2days ago",
    -- 3天前统计
    SUM(CASE WHEN TRUNC(activity_date) = TRUNC(SYSDATE) - 3 THEN activity_count ELSE 0 END) AS "3 days ago",
    -- 4天前统计
    SUM(CASE WHEN TRUNC(activity_date) = TRUNC(SYSDATE) - 4 THEN activity_count ELSE 0 END) AS "4 days ago",
    -- 5天前统计
    SUM(CASE WHEN TRUNC(activity_date) = TRUNC(SYSDATE) - 5 THEN activity_count ELSE 0 END) AS "5 days ago",
    -- 6天前统计
    SUM(CASE WHEN TRUNC(activity_date) = TRUNC(SYSDATE) - 6 THEN activity_count ELSE 0 END) AS "6 days ago"
FROM
    -- 替换为你的实际仓库活动表名
    warehouse_activity
WHERE
    -- 提前过滤近7天数据，优化查询性能
    TRUNC(activity_date) BETWEEN TRUNC(SYSDATE) - 6 AND TRUNC(SYSDATE)
GROUP BY
    facility_activity
ORDER BY
    facility_activity;

关键注意点 #

1. 日期截断：用TRUNC()去掉日期的时分秒部分，确保仅按自然日维度统计，避免时间差异导致的数据漏算
2. 列名替换：将warehouse_activity、facility_activity、activity_date、activity_count替换为你表中的真实字段名（这是ORA-00904的高发诱因）
3. 类型一致性：确保activity_count为数值类型，CASE WHEN的ELSE分支返回0（而非NULL或字符串），避免ORA-01790类型不匹配错误
4. 性能优化：WHERE子句提前过滤7天内的数据，避免全表扫描

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