Oracle sql组合表和做计数

Question

如何计算项目名称湖人队的总项目参与(即项目计数)，并在12/25-27/2022之间为所有员工显示UserAc和他们的总PJCount参加客户会议?

同样，如果员工UserAc没有出现在projectparticipation中。当它们存在于EMPLOYEE表中时，它还需要在最终输出中显示它们的UserAc和PJCount为0。

表员工:

UserAc
Ken1
John1
Alex1
Dan1

表ProjectParticipate

PJName    UserAc    PJRn    PDate        PJCount
Lakers    Ken1      0200    12/25/2022   5.3
Lakers    Ken1      0200    12/28/2022   8.7
Lakers    Ken1      0200    12/27/2022   4.3
Bulls     Ken1      0200    12/25/2022   4
Lakers    John1     0100    12/25/2022   6
Lakers    Alex1     0200    12/25/2022   3

表ProjectGuide

PJName    PJNumber    NumberExplan
Lakers    0100        Phone call
Lakers    0200        Client meeting
Bulls     0100        Phone call
Bulls     0200        Team meeting
State     0100        Discussion
State     0200        Documentation

这是我开发的当前查询

Select 
UserAc,
COUNT(PJCount) 
from
ProjectParticipate
Where 
PJRn = ‘0200’ and PDate BETWEEN 12/25/2022 and 12/27/2022
--it seems to need to use TABLE ProjectGuide to know Project:Lakers, PJNumber:0200 = Client meeting instead of just PJRn = 0200
Group by 
UserAc

我不知道如何使用和组合3个表来满足要求。

以下是预期结果

UserAc      PJCount
Ken1        9.6
John1       0
Alex1       3
Dan1        0

非常感谢你的帮助。

Answer 1

您可以使用左连接在12月25日至27日之间仅获取'Client meeting'和PDate中的PJCount。您需要使用SUM()来代替COUNT()。

Schema and insert statement:

CREATE TABLE EMPLOYEE(UserAc VARCHAR(50));
INSERT INTO EMPLOYEE VALUES('Ken1');
INSERT INTO EMPLOYEE VALUES('John1');
INSERT INTO EMPLOYEE VALUES('Alex1');
INSERT INTO EMPLOYEE VALUES('Dan1');
CREATE TABLE ProjectParticipate(PJName VARCHAR(50), UserAc VARCHAR(50), PJRn VARCHAR(50), PDate DATE, PJCount FLOAT);
INSERT INTO ProjectParticipate VALUES('Lakers',    'Ken1',      '0200',   DATE'2022-12-25',   5.3);
INSERT INTO ProjectParticipate VALUES('Lakers',    'Ken1',      '0200',    DATE'2022-12-28',   8.7);
INSERT INTO ProjectParticipate VALUES('Lakers',    'Ken1',      '0200',    DATE'2022-12-27',   4.3);
INSERT INTO ProjectParticipate VALUES('Bulls',     'Ken1',      '0200',    DATE'2022-12-25',   4);
INSERT INTO ProjectParticipate VALUES('Lakers',    'John1',     '0100',    DATE'2022-12-25',   6);
INSERT INTO ProjectParticipate VALUES('Lakers',    'Alex1',     '0200',    DATE'2022-12-25',   3);
CREATE Table ProjectGuide(PJName VARCHAR(50),    PJNumber    VARCHAR(50), NumberExplan VARCHAR(50))
INSERT INTO ProjectGuide VALUES('Lakers','0100','Phone call');
INSERT INTO ProjectGuide VALUES('Lakers','0200','Client meeting');
INSERT INTO ProjectGuide VALUES('Bulls','0100','Phone call');
INSERT INTO ProjectGuide VALUES('Bulls','0200','Team meeting');
INSERT INTO ProjectGuide VALUES('State','0100','Discussion');
INSERT INTO ProjectGuide VALUES('State','0200','Documentation');

查询:

Select 
E.UserAc,
SUM(case when PJNumber is not null then PP.PJCount else 0 end ) PJCount
from
EMPLOYEE E LEFT JOIN ProjectParticipate PP ON E.UserAc=PP.UserAc
and PP.PDate BETWEEN DATE'2022-12-25' and DATE'2022-12-27'
LEFT JOIN ProjectGuide PG ON PP.PJName=PG.PJName AND PP.PJRn=PG.PJNumber 
and PG.NumberExplan = 'Client meeting'

Group by 
E.UserAc

输出

PJCOUNTKen19.6300

使用union。不是count，而是sum，但你错过了EMPLOYEE和ProjectGuide之间的联系。你需要找到或创造这种关系然后用内联视图

包装

SELECT UserAc, Count(cnt)
FROM 
( -- START INLINE VIEW
Select 
UserAc,
SUM(PJCount) cnt
from
ProjectParticipate
Where 
PJRn = '0200' and 
(PDate BETWEEN to_date('12/25/2022', 'MM/DD/YYYY') and to_date('12/27/2022', 'MM/DD/YYYY'))
Group by 
UserAc
UNION
Select 
st.UserAc,
0 cnt
from
ProjectGuide pg inner join 
some_table st on pg.PJNumber = st.PJNumber -- <-- you nee to find or define this relation
Group By 
st.UserAc
) T  -- END INLINE VIEW
GROUP BY 
UserAc

Union抑制精确记录。因此，John/9和John/0将返回2条记录，sum将是正确的。这就是为什么你再一次group by。

使用IN子句查找代表客户会议的PJ名称/号码。使用外部连接来包含未参加的用户。(并使用正确的日期文字。你得到的是SUM的和，而不是COUNT。

select e.userac, coalesce(pp.sum_pjcount, 0) as total
from employee e
left outer join
(
select userac, sum(pjcount) as sum_pjcount
from projectparticipate
where pdate between date '2022-12-25' and date '2022-12-27'
and (pjname, pjrn) in 
(
select pjname, pjnumber
from projectguide
where numberexplan = 'Client meeting'
)
group by userac
) pp on pp.userac = e.userac
order by e.userac;