oracle case when then 用法

最新推荐文章于 2023-02-22 12:06:34 发布

taiweipeng

最新推荐文章于 2023-02-22 12:06:34 发布

阅读量463

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： Oracle CASE WHEN THEN

本文链接：https://blog.youkuaiyun.com/taiweipeng/article/details/84662080

oracle 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询用于从TT_PICKUP_DEPOT_BATCH_SHOW和TM_DEPARTMENT表中汇总多个指标数据，包括计划数量、实际数量等，并通过CASE WHEN语句对提前完成和延迟完成的情况进行统计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
D.DEPT_CODE,
SUM(T.GATHER_ZONE_PLAN_NUM),
SUM(T.ZONE_PLAN_NUM),
SUM(T.GATHER_ZONE_ACTUAL_NUM),
SUM(T.ZONE_ACTUAL_NUM),
SUM(T.MISS_LOAD_BILL_NUM),
SUM(T.SURPLUS_DEPART_NUM),
SUM(T.NOT_DEPART_NUM),
SUM(T.LATE_NESS_DEPART_NUM),
SUM(CASE WHEN TRUNC(TO_NUMBER(T.ACTUAL_END_TM-T.PLAN_END_TM)*1440)<=? then 1 WHEN T.ACTUAL_END_TM IS NULL THEN 1 ELSE 0 END) AS BEFORE,
SUM(CASE WHEN TRUNC(TO_NUMBER(T.ACTUAL_END_TM-T.PLAN_END_TM)*1440)>? then 1 ELSE 0 END) AS LATE,
TRUNC(AVG(CASE WHEN T.ACTUAL_END_TM IS NOT NULL THEN TO_NUMBER(T.PLAN_END_TM - T.PLAN_BEGIN_TM) * 1440 ELSE NULL END)) AS PLANCOST,
TRUNC(AVG(TO_NUMBER(T.ACTUAL_END_TM-T.PLAN_BEGIN_TM)*1440))AS ACTUALCOST
FROM TT_PICKUP_DEPOT_BATCH_SHOW T, TM_DEPARTMENT D
WHERE T.ZONE_CODE = D.DEPT_CODE