全员加入集团

Question

我的查询面临一个逻辑问题。

我有两个表Table1和Table2，，其中Table1包括：

• 待加的value
• Id将按
• Code持有Table2的外键。

而Table2由以下内容组成

• Code
• Des代码的文本描述

我想要做的是，按Table1.Id分组，在Table2.Code上进行完全连接，但是，对于每个结果组，我想为查询生成的每个组显示来自Table2的所有行。

示例代码：

SELECT
    Table2.Code, Table1.Id, Table2.DES, 
    SUM(Table1.Value) AS SUM_VAL
FROM 
(
    SELECT 'A' AS Code, 1 AS Id, 10 AS Value FROM DUAL UNION
    SELECT 'A' AS Code, 2 AS Id, 20 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 1 AS Id, 10 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 1 AS Id, 30 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 2 AS Id, 50 AS Value FROM DUAL UNION
    SELECT 'C' AS Code, 1 AS Id, 40 AS Value FROM DUAL UNION
    SELECT 'C' AS Code, 2 AS Id, 60 AS Value FROM DUAL UNION
    SELECT 'D' AS Code, 1 AS Id, 20 AS Value FROM DUAL

) Table1
FULL JOIN
(
    SELECT 'A' AS Code, 'This is A' AS DES FROM DUAL UNION
    SELECT 'B' AS Code, 'This is B' AS DES FROM DUAL UNION
    SELECT 'C' AS Code, 'This is C' AS DES FROM DUAL UNION
    SELECT 'D' AS Code, 'This is D' AS DES FROM DUAL
) Table2
ON Table1.Code = Table2.Code
GROUP BY
    Table2.Code, Table1.Id, Table2.DES
ORDER BY
    Table2.Code, Table1.Id ASC

结果：

A   1   This is A   10
A   2   This is A   20
B   1   This is B   40
B   2   This is B   50
C   1   This is C   40
C   2   This is C   60
D   1   This is D   20

必需的结果：

A   1   This is A   10
A   2   This is A   20
B   1   This is B   40
B   2   This is B   50
C   1   This is C   40
C   2   This is C   60
D   1   This is D   20
D   2   This is D   0    <- This is the target

Answer 1

您必须以某种方式显示值对(D,2)例如。通过使用可能的值创建代码列表并将NULL转换为0

  SELECT code.code,
         code.id,
         des.des,
         NVL (SUM (val.value), 0) sum_val
    FROM (SELECT 'A' code, 1 id FROM DUAL
          UNION
          SELECT 'A', 2 FROM DUAL
          UNION
          SELECT 'B', 1 FROM DUAL
          UNION
          SELECT 'B', 2 FROM DUAL
          UNION
          SELECT 'C', 1 FROM DUAL
          UNION
          SELECT 'C', 2 FROM DUAL
          UNION
          SELECT 'D', 1 FROM DUAL
          UNION
          SELECT 'D', 2 FROM DUAL) code
         INNER JOIN (SELECT 'A' code, 'This is A' des FROM DUAL
                     UNION
                     SELECT 'B', 'This is B' FROM DUAL
                     UNION
                     SELECT 'C', 'This is C' FROM DUAL
                     UNION
                     SELECT 'D', 'This is D' FROM DUAL) des
            ON code.code = des.code
         LEFT OUTER JOIN (SELECT 'A' code, 1 id, 10 VALUE FROM DUAL
                          UNION ALL
                          SELECT 'A', 2, 20 FROM DUAL
                          UNION ALL
                          SELECT 'B', 1, 10 FROM DUAL
                          UNION ALL
                          SELECT 'B', 1, 30 FROM DUAL
                          UNION ALL
                          SELECT 'B', 2, 50 FROM DUAL
                          UNION ALL
                          SELECT 'C', 1, 40 FROM DUAL
                          UNION ALL
                          SELECT 'C', 2, 60 FROM DUAL
                          UNION ALL
                          SELECT 'D', 1, 20 FROM DUAL) val
            ON code.code = val.code AND code.id = val.id
GROUP BY code.code, code.id, des.des
ORDER BY code, id

UNION ALL在val中使用，因为可能会发生重复。

不需要FULL OUTER JOIN。

Answer 2

如果您想要id和value的所有组合，那么使用cross join获取行，使用left join来导入其余的值：

select t2.code, i.value, t2.desc, coalesce(cnt, 0) as cnt
from (select distinct id from table1) i cross join
     table2 t2 left join
     (select id, value, count(*) as cnt
      from table1
      group by id, value
     ) iv
     on iv.id = i.id and iv.code = t2.code

这应该比手动列出所有组合要简单得多。

Answer 3

SELECT
    Table2.Code, Table2.NAT, Table2.DES, 
    SUM(Table1.Value) AS SUM_VAL
FROM 
(
    SELECT 'A' AS Code, 'QA' AS Id, 10 AS Value FROM DUAL UNION
    SELECT 'A' AS Code, 'NQA' AS Id, 20 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 'QA' AS Id, 10 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 'QA' AS Id, 30 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 'NQA' AS Id, 50 AS Value FROM DUAL UNION
    SELECT 'C' AS Code, 'QA' AS Id, 40 AS Value FROM DUAL UNION
    SELECT 'C' AS Code, 'NQA' AS Id, 60 AS Value FROM DUAL UNION
    SELECT 'D' AS Code, 'QA' AS Id, 20 AS Value FROM DUAL

) Table1
FULL JOIN
(
    SELECT 'QA' NAT,'A' AS Code, 'This is A' AS DES FROM DUAL UNION
    SELECT 'QA' NAT,'B' AS Code, 'This is B' AS DES FROM DUAL UNION
    SELECT 'QA' NAT,'C' AS Code, 'This is C' AS DES FROM DUAL UNION
    SELECT 'QA' NAT,'D' AS Code, 'This is D' AS DES FROM DUAL
    UNION
    SELECT 'NQA' NAT,'A' AS Code, 'This is A' AS DES FROM DUAL UNION
    SELECT 'NQA' NAT,'B' AS Code, 'This is B' AS DES FROM DUAL UNION
    SELECT 'NQA' NAT,'C' AS Code, 'This is C' AS DES FROM DUAL UNION
    SELECT 'NQA' NAT,'D' AS Code, 'This is D' AS DES FROM DUAL
) Table2
on TABLE2.NAT = TABLE1.ID
      AND Table2.Code= Table1.Code
GROUP BY
    Table2.Code, Table2.NAT, Table2.DES
ORDER BY
    Table2.Code, Table2.NAT ASC