时间戳

首页 > 解决方案 > 带条件的 Oracle SQL 求和

sql - 带条件的 Oracle SQL 求和

问题描述

我正在尝试按部门和月份汇总（当前时间）、（病假时间）。这笔款项必须包括我在其中工作的部门，包括案例陈述。我自己尝试过多次，但是我似乎无法让它按预期工作。

EMP_NO     Wage_type     Hours_worked   Department      OBJID(UNIQUE)  DATE
10011      Normal        14             1063            ABC116         04/01/18
10011      Normal        07             1063            ABC117         05/01/18
10011      SICK          21             1063            ABC118         01/02/18
10030      Normal        12.5           1054            ABC119         02/02/18
10030      SICK          7              1054            ABC120         03/02/18
10030      SICK          7              1054            ABC121         04/02/18

原始数据等的示例代码：

Select 
 TPDRS.OBJID
,TPDRS.WAGE_HOURS
,(case 
    when TPDRS.ORG_CODE = 1010 then  'Trolley P1'
    when TPDRS.ORG_CODE = 1011 then  'Trolley P2'
    when TPDRS.ORG_CODE = 1053 then  'Trolley P3'
    when TPDRS.ORG_CODE between 1054 and 1057 then  'Trolley P4'
    when TPDRS.ORG_CODE between 1040 and 1047 then  'Trolley P5'
    when TPDRS.ORG_CODE in ('1063','1064','1065','1068') then 'Trolley P6'
    else 'NOT REQUIRED'
        end) as Department
,TPDRS.EMP_NO
,TPDRS.ACCOUNT_DATE
,EXTRACT(Month from TPDRS.ACCOUNT_DATE) "Month_Number"
,EXTRACT(Year from TPDRS.ACCOUNT_DATE) "Year"

from TrolleyParkAttendant  TPDRS
WHERE TPDRS.ACCOUNT_DATE > '01/DEC/2017'
 AND (TPDRS.WAGE_GRP_DB IN ( 'O', 'N' ) or WAGE_CODE = 'SICK')

Expected Results:
Department    HoursSick     HoursPresent     Month
Trolley P6      0             21              01
Trolley P6     21             0               02 
Trolley P3     14             12.5            02

任何援助将不胜感激

标签： sqloraclereport

解决方案

您需要条件聚合，以避免重复将表达式嵌套在派生表中（Oracle中的内联视图）：

select
    Department
  ,sum(case when Wage_type = 'SICK' then Hours_worked else 0 end) as HoursSick
  ,sum(case when Wage_type = 'Normal' then Hours_worked else 0 end) as HoursPresent
  ,"Month_Number"
  ,"Year"
from
 (
    Select 
     (case 
        when TPDRS.ORG_CODE = 1010 then  'Trolley P1'
        when TPDRS.ORG_CODE = 1011 then  'Trolley P2'
        when TPDRS.ORG_CODE = 1053 then  'Trolley P3'
        when TPDRS.ORG_CODE between 1054 and 1057 then  'Trolley P4'
        when TPDRS.ORG_CODE between 1040 and 1047 then  'Trolley P5'
        when TPDRS.ORG_CODE in ('1063','1064','1065','1068') then 'Trolley P6'
        else 'NOT REQUIRED'
            end) as Department
    ,EXTRACT(Month from TPDRS.ACCOUNT_DATE) "Month_Number"
    ,EXTRACT(Year from TPDRS.ACCOUNT_DATE) "Year"

    from TrolleyParkAttendant  TPDRS
    WHERE TPDRS.ACCOUNT_DATE > '01/DEC/2017'
     AND (TPDRS.WAGE_GRP_DB IN ( 'O', 'N' ) or WAGE_CODE = 'SICK')
 ) dt
group by
    Department
  ,"Month_Number"
  ,"Year"

推荐阅读