oracle 同一职员查询多行返回结果

3j86kqsm 于 2023-05-16 发布在 Oracle

关注(0)|答案(1)|浏览(154)

我有一个表，其中包含多个元素值，每个元素都有相应的输入值：

EE   ELEMENT_NAME     RESULT_VALUE   EFFECTIVE DATE   INPUT VALUE
-----------------------------------------------------------------
12   Overtime           1000           10-APR-2023      earning
12   Overtime           10             10-APR-2023      Hours
12   REGULAR_RETRO      110            10-APR-2023      earning
11   REGULAR_RETRO      120            10-apr-2023      earning

所需输出为：

EE   REGULAR_RETRO   OVERTIME_PAID          OVERTIME_Hours_taken
----------------------------------------------------------------
12   110                1000                        10  
11   120

此查询返回两行：

SELECT
    ee person_number,
    SUM(CASE 
            WHEN peen.element_name IN 'REGULAR RETRO'
                THEN (RESULT_VALUE) 
        END) REGULAR_RETRO,
    SUM(CASE 
            WHEN peen.element_name IN 'OVERTIME' AND input_value = 'Hours'
                THEN (RESULT_VALUE)
        END) OVERTIME_hours,
    SUM(CASE 
            WHEN peen.element_name IN 'OVERTIME' AND input_value =  'earning'
                THEN (RESULT_VALUE) 
        END) OVERTIME_paid 
FROM
    PER_ALL_PEOPLE_F PAPF
    PER_ELEMENT_NAME PEEN,
    PEr_ELEMENT_TYPE PET
WHERE
    PAPF.PERSON_ID = PEEN.PERSON_ID
    AND PEEN.ELEMENT_ID = PET.ELEMENT_ID
    AND PET.INPUT_VALUE IN ('Earning','Hours')
    AND peen.element_name IN ('REGULAR RETRO', 'OVERTIME')
GROUP BY
    person_number

但是这个查询为ee#12返回了两行，因为他同时输入了name - earning和hours，但是应该是一行-

EE   REGULAR_RETRO   OVERTIME_PAID          OVERTIME_Hours_taken
-----------------------------------------------------------------
12   110                1000                        0 
12   110                 0                          10  
11   120

oracle

来源：https://stackoverflow.com/questions/76205625/query-returning-result-in-multiple-rows-for-same-employee

1条答案

按热度按时间

toiithl61#

如果你已经准备好了问题中的数据-你可以将行透视到ccolumns中，使用case表达式来管理数据并按EE分组聚合以获得预期的结果：

WITH          --  Sample Data
    tbl (EE, ELEMENT_NAME, RESULT_VALUE, EFFECTIVE_DATE, INPUT_VALUE) AS
        (   Select 12, 'Overtime', 1000, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'earning' From Dual Union All
            Select 12, 'Overtime',   10, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'Hours' From Dual Union All
            Select 12, 'REGULAR_RETRO',  110, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'earning' From Dual Union All
            Select 11, 'REGULAR_RETRO',  120, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'earning' From Dual 
        )
--
--  M a i n   S Q L .
SELECT  EE, 
        Max(REGULAR_RETRO) "REGULAR_RETRO", 
        Max(OVERTIME_PAID) "OVERTIME_PAID",
        Max(OVERTIME_HOURS_TAKEN) "OVERTIME_HOURS_TAKEN"
FROM    (   Select  EE, 
                    Case When INPUT_VALUE = 'earning' THEN REGULAR_RETRO_PAID End "REGULAR_RETRO", 
                    Case When INPUT_VALUE = 'earning' THEN OVERTIME_PAID End "OVERTIME_PAID",
                    Case When INPUT_VALUE = 'Hours' THEN OVERTIME_HOURS_TAKEN End "OVERTIME_HOURS_TAKEN"
            From    tbl
            PIVOT   (   Sum(RESULT_VALUE) "PAID", Sum(RESULT_VALUE) "HOURS_TAKEN"
                  For ELEMENT_NAME IN('REGULAR_RETRO' "REGULAR_RETRO", 'Overtime' "OVERTIME" )
                )
        )
GROUP BY EE
ORDER BY EE Desc
--
--  R e s u l t :
--          EE REGULAR_RETRO OVERTIME_PAID OVERTIME_HOURS_TAKEN
--  ---------- ------------- ------------- --------------------
--          12           110          1000                   10 
--          11           120

赞(0）回复(0）举报 2023-05-16

我来回答

oracle 同一职员查询多行返回结果

1条答案

相关问题

热门标签

最新问答