Oracle SQL查询以获取员工的元素总和

wnvonmuf 于 2023-05-16 发布在 Oracle

关注(0)|答案(2)|浏览(102)

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

EE   ELEMENT_NAME     RESULT_VALUE   EFFECTIVE DATE   INPUT VALUE
10   REGULAR RETRO    3457           10-APR-2023      earning
10   REGULAR RETTO    -3454          10-APR-2023      earning
10   REGULAR SALAR    34000          10-APR-2023      Pay
11   REGULAR SALAR    670000         10-APR-2023      earning
11   DENTAL SALAR     100            10-APR-2023      Pay
11   HEALTH SALAR     2000           10-APR-2023      Pay
11   Car     SALAR    2000           10-APR-2023      Pay
12   EE       RETRO   -3000          10-APR-2023      Pay
12   REGULAR RETRO    1000           10-APR-2023      earning
12   REGULAR RETRO    -890           10-APR-2023      earning
12   Tax RETRO        -890           10-APR-2023      earning

所需输出为：

EE   REGULAR_RETRO   REGULAR_SALAR   DENTAL_SALAR   HEALTH_SALAR   EE_RETRO
10   3               34000
11                   670000          100            2000
12   110                                                           -3000

下面我使用的查询只针对我在查询的“where子句”中定义的特定元素，但是我得到了同一个雇员的多个行，尽管我使用了group by。输出也没有对retros值求和（常规retros），而是给出重复的行。
我使用的查询是：

select ee person_number,
    case when peen.element_name in 'REGULAR RETRO'
    then sum (RESULT_VALUE) REGULAR_RETRO,
    case when peen.element_name in 'REGULAR SALAR'
    then sum (RESULT_VALUE) REGULAR_SALAR,
    case when peen.element_name in 'DENTAL SALAR'
    then sum (RESULT_VALUE) DENTAL_SALAR,
    case when peen.element_name in 'HEALTH SALAR'
    then sum (RESULT_VALUE) HEALTH_SALAR,
    case when peen.element_name in 'EE       RETRO'
    then sum (RESULT_VALUE) EE_RETRO

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 ('Pay','Earning')
and peen.element_name in
(
'REGULAR SALAR',
'DENTAL SALAR',
'HEALTH SALAR',
'EE       RETRO',
'REGULAR RETRO'
)
 group by person_number,
 peen.element_name

oracle

来源：https://stackoverflow.com/questions/76192614/oracle-sql-query-to-get-the-sum-of-elements-for-an-employee

2条答案

按热度按时间

anauzrmj1#

这可以通过使用group by和sum()的条件聚合来完成：

select EE,
       sum(case when ELEMENT_NAME = 'REGULAR RETRO' then RESULT_VALUE else 0 end) as REGULAR_RETRO,
       sum(case when ELEMENT_NAME = 'REGULAR SALAR' then RESULT_VALUE else 0 end) as REGULAR_SALAR,
       sum(case when ELEMENT_NAME = 'DENTAL SALAR' then RESULT_VALUE else 0 end) as DENTAL_SALAR,
       sum(case when ELEMENT_NAME = 'HEALTH SALAR' then RESULT_VALUE else 0 end) as HEALTH_SALAR,
       sum(case when ELEMENT_NAME = 'EE RETRO' then RESULT_VALUE else 0 end) as EE_RETRO
from mytable
group by EE

结果：
| EE|常规_RETRO|公司简介|牙科_SALAR|公司简介|EE_RETRO|
| --------------|--------------|--------------|--------------|--------------|--------------|
| 10个|三|三万四千|0| 0| 0|
| 十一|0|六十七万|一百|二千年|0|
| 十二岁|一百一十|0| 0| 0|三千|
Demo here

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

u5i3ibmn2#

修复查询中CASE的语法，并从“group by”中删除peen.element_name应该可以解决这个问题。

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