Oracle连接不同表中的行，并将其显示在一个列中

Question

如何通过组合这些表获得如下结果?

预期结果为

PDate        ProjectGDetails   ProjectTDetails   PJCount   Notes
01/25/2022   A+B+Good              B+B+Good         3       Yes
01/27/2022   D+B+Bad               C+Good           2       No
01/25/2022   A+Good                D+D+Bad          1       No

我只需要获取2022年1月项目Lakers的用户Ken1的数据。并在ProjectGrade和ProjectTier的表格中用"+"连接PJGrade1、ProjectGrade2、comments的数据。如果PJGrade1, ProjectGrade2, comments中的某些数据为空，则不能在显示中添加' + '，例如+ a +Good。

表ProjectDetails

PJName    UserAc    PJRn    PDate         PJCount    Notes
Lakers    Ken1      0201    01/25/2022       3        Yes
Lakers    Ken1      0202    12/28/2022       5        No
Lakers    Ken1      0203    01/27/2022       2        Yes
Bulls     Ken1      0201    01/25/2022       4        Yes
Lakers    John1     0101    01/25/2022       7        No
Lakers    Alex1     0201    05/25/2022       2        Yes
Lakers    Ken1      0204    01/25/2022       1        No

表ProjectGrade

PJName    PJNumber    PJDetail         PJGrade1 PJGrade2  Remark
Lakers    0201        Client meetings     A         B      Good
Lakers    0101        Phone call          C         C       Bad
Lakers    0202        Client meeting      B         C       Bad
Lakers    0203        Client meeting      D         B       Bad
Lakers    0204        Client meeting                A       Good
Bulls     0201        Phone call.         A         B       Good
Bulls     0202        Team meeting        C         C       Bad
State     0101        Discussion          B         A       Good
State     0201        Documentation       B         B       Good

表ProjectTier

PJName    PJNumber    PJDetail         PJGrade1 PJGrade2   Remark 
Lakers    0201        Client meetings     B         B       Good
Lakers    0101        Phone call          F         C       Bad
Lakers    0202        Client meeting      B         B       Good
Lakers    0203        Client meeting      C                 Good
Lakers    0204        Client meeting      D         D       Bad
Bulls     0201        Phone call.         A         A       Good
Bulls     0202        Team meeting        B         A       Good
State     0101        Discussion          C         B       Good
State     0201        Documentation       C         F       Bad

这是我的当前查询

Select 
UserAc,
PDate,
PJCount,
Notes
PJGrade1 + ‘+’, PJGrade2 + ‘+’ , Remarks As ProjectGDetails
from
ProjectDetails, ProjectGrade, ProjectTier
Where 
PJName = ‘Lakers’ and PDate BETWEEN DATE ‘01/01/2022, and DATE ‘01/31/2022’

我可以知道如何组合这些表并得到预期的结果吗?

非常感谢你的帮助

Answer 1

使用说明:

• 您没有UserAC = 'Ken1'的过滤器;
• '而不是‘用于字符串字量;
• ||而不是+用于字符串连接操作符;
• 日期文字的格式是DATE 'YYYY-MM-DD'而不是DATE ‘MM/DD/YYYY‘;和
• 添加连接条件，而不是使用CROSS JOIN(通过遗留的逗号连接语法)

类似:

SELECT d.PDate,
g.PJGrade1 || '+' || g.PJGrade2 || '+' || g.Remark As ProjectGDetails,
t.PJGrade1 || '+' || t.PJGrade2 || '+' || t.Remark As ProjectTDetails,
d.PJCount,
d.Notes
FROM   ProjectDetails d
INNER JOIN ProjectGrade g
ON (d.PJName = g.PJName AND d.PJRn = g.PJNumber)
INNER JOIN ProjectTier t
ON (d.PJName = t.PJName AND d.PJRn = t.PJNumber)
WHERE  d.PJName =  'Lakers'
AND    d.UserAC =  'Ken1'
AND    d.PDate  >= DATE '2022-01-01'
AND    d.PDate  <  DATE '2022-02-01'

Answer 2

也许这就是你需要的。这里的WITH子句仅用于创建示例数据，这些不是答案的一部分。

收集grade和tier(子查询)的组合数据，并将它们与project连接。

你可以从主SELECT中排除你不需要的列，你可以改变where子句来查看不同的数据。

希望这能解决你的问题。

WITH ProjectDetails AS
(
SELECT 'Lakers' "PJNAME", 'Ken1' "USERAC", '0201' "PJRN", To_Date('01/25/2022', 'mm/dd/yyyy') "PDATE", 3 "PJCOUNT", 'Yes' "NOTES" FROM DUAL UNION ALL
SELECT 'Lakers' "PJNAME", 'Ken1' "USERAC", '0202' "PJRN", To_Date('12/28/2022', 'mm/dd/yyyy') "PDATE", 5 "PJCOUNT", 'No' "NOTES"  FROM DUAL UNION ALL
SELECT 'Lakers' "PJNAME", 'Ken1' "USERAC", '0203' "PJRN", To_Date('01/27/2022', 'mm/dd/yyyy') "PDATE", 2 "PJCOUNT", 'Yes' "NOTES" FROM DUAL UNION ALL
SELECT 'Bulls'  "PJNAME", 'Ken1'  "USERAC", '0201' "PJRN", To_Date('01/25/2022', 'mm/dd/yyyy') "PDATE", 4 "PJCOUNT", 'Yes' "NOTES" FROM DUAL UNION ALL
SELECT 'Lakers' "PJNAME", 'John1' "USERAC", '0101' "PJRN", To_Date('01/25/2022', 'mm/dd/yyyy') "PDATE", 7 "PJCOUNT", 'No' "NOTES" FROM DUAL UNION ALL
SELECT 'Lakers' "PJNAME", 'Alex1' "USERAC", '0201' "PJRN", To_Date('05/25/2022', 'mm/dd/yyyy') "PDATE", 2 "PJCOUNT", 'Yes' "NOTES" FROM DUAL UNION ALL
SELECT 'Lakers' "PJNAME", 'Ken1'  "USERAC", '0204' "PJRN", To_Date('01/25/2022', 'mm/dd/yyyy') "PDATE", 1 "PJCOUNT", 'No' "NOTES" FROM DUAL  
),
ProjectGrade AS
(
SELECT  'Lakers' "PJNAME",  '0201'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'A'  "PJGRADE1",    'B' "PJGRADE2",     'Good' "REMARK"     FROM DUAL UNION ALL
SELECT  'Lakers' "PJNAME",  '0101'  "PJNUMBER",     'Phone call'      "PJDETAIL",   'C'  "PJGRADE1",    'C' "PJGRADE2",     'Bad'  "REMARK"     FROM DUAL UNION ALL
SELECT  'Lakers' "PJNAME",  '0202'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'B'  "PJGRADE1",    'C' "PJGRADE2",     'Bad'  "REMARK"     FROM DUAL UNION ALL
SELECT  'Lakers' "PJNAME",  '0203'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'D'  "PJGRADE1",    'B' "PJGRADE2",     'Bad'  "REMARK"     FROM DUAL UNION ALL
SELECT  'Lakers' "PJNAME",  '0204'  "PJNUMBER",     'Client meetings' "PJDETAIL",   Null "PJGRADE1",    'A' "PJGRADE2",     'Good' "REMARK"     FROM DUAL UNION ALL
SELECT  'Bulls'  "PJNAME",  '0201'  "PJNUMBER",     'Phone call'      "PJDETAIL",   'A'  "PJGRADE1",    'B' "PJGRADE2",     'Good' "REMARK"     FROM DUAL UNION ALL
SELECT  'Bulls'  "PJNAME",  '0202'  "PJNUMBER",     'Team meeting'    "PJDETAIL",   'B'  "PJGRADE1",    'C' "PJGRADE2",     'Bad'  "REMARK"     FROM DUAL UNION ALL
SELECT  'State'  "PJNAME",  '0101'  "PJNUMBER",     'Discussion'      "PJDETAIL",   'A'  "PJGRADE1",    'A' "PJGRADE2",     'Good' "REMARK"     FROM DUAL UNION ALL
SELECT  'State'  "PJNAME",  '0201'  "PJNUMBER",     'Documentation'   "PJDETAIL",   'A'  "PJGRADE1",    'B' "PJGRADE2",     'Good' "REMARK"     FROM DUAL 
),
ProjectTier AS
(
SELECT  'Lakers' "PJNAME",  '0201'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'B'  "PJGRADE1",    'B'  "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
SELECT  'Lakers' "PJNAME",  '0101'  "PJNUMBER",     'Phone call'      "PJDETAIL",   'F'  "PJGRADE1",    'C'  "PJGRADE2",    'Bad'  "REMARK"     FROM DUAL UNION ALL
SELECT  'Lakers' "PJNAME",  '0202'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'B'  "PJGRADE1",    'B'  "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
SELECT  'Lakers' "PJNAME",  '0203'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'C'  "PJGRADE1",    Null "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
SELECT  'Lakers' "PJNAME",  '0204'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'D'  "PJGRADE1",    'D'  "PJGRADE2",    'Bad'  "REMARK"     FROM DUAL UNION ALL
SELECT  'Bulls'  "PJNAME",  '0201'  "PJNUMBER",     'Phone call'      "PJDETAIL",   'A'  "PJGRADE1",    'A'  "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
SELECT  'Bulls'  "PJNAME",  '0202'  "PJNUMBER",     'Team meeting'    "PJDETAIL",   'B'  "PJGRADE1",    'A'  "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
SELECT  'State'  "PJNAME",  '0101'  "PJNUMBER",     'Discussion'      "PJDETAIL",   'C'  "PJGRADE1",    'B'  "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
SELECT  'State'  "PJNAME",  '0201'  "PJNUMBER",     'Documentation'   "PJDETAIL",   'C'  "PJGRADE1",    'F'  "PJGRADE2",    'Bad'  "REMARK"     FROM DUAL 
)
--  ********************************************************************************************
SELECT 
p.PDATE "PDATE",  
REPLACE(LTRIM(gt.PJ_G1 || '+' || gt.PJ_G2 || '+' || gt.PJ_G_REM, '+'), '++', '+') "GRADE", 
REPLACE(LTRIM(gt.PJ_T1 || '+' || gt.PJ_T2 || '+' || gt.PJ_T_REM, '+'), '++', '+') "TIER", 
p.PJCOUNT "PJ_COUNT",
p.NOTES "PJ_NOTES",

p.PJRN "PJRN",
p.PJNAME "PJNAME",
p.USERAC "USERAC"
FROM 
ProjectDetails p
INNER JOIN          -- collect data from grade and tier combined
(
SELECT
g.PJNAME "PJNAME",
g.PJNUMBER "PJRN",
g.PJGRADE1 "PJ_G1",
g.PJGRADE2 "PJ_G2",
g.REMARK "PJ_G_REM",
t.PJGRADE1 "PJ_T1",
t.PJGRADE2 "PJ_T2",
t.REMARK "PJ_T_REM"
FROM
ProjectGrade g
INNER JOIN
ProjectTier t ON(t.PJNUMBER = g.PJNUMBER  And g.PJNAME = t.PJNAME)    
) gt ON(p.PJNAME = gt.PJNAME And p.PJRN = gt.PJRN)
WHERE               -- you can changge the where clause to whatever suites you the best
p.PJNAME =  'Lakers' AND      
p.PDATE  Between To_Date('01/01/2022', 'mm/dd/yyyy') AND To_Date('01/31/2022', 'mm/dd/yyyy') 
ORDER BY
p.PJCOUNT DESC, p.PDATE, p.PJRN
--  
--  R e s u l t
--  
--  PDATE     GRADE    TIER       PJ_COUNT PJ_NOTES PJRN PJNAME USERAC
--  --------- -------- -------- ---------- -------- ---- ------ ------
--  25-JAN-22 C+C+Bad  F+C+Bad           7 No       0101 Lakers John1  
--  25-JAN-22 A+B+Good B+B+Good          3 Yes      0201 Lakers Ken1   
--  27-JAN-22 D+B+Bad  C+Good            2 Yes      0203 Lakers Ken1   
--  25-JAN-22 A+Good   D+D+Bad           1 No       0204 Lakers Ken1 

我删除了前导加号和重复加号(如果有的话)，如果在grade和/或tier表中有null，可能会发生。问候…