我面对我的查询逻辑问题。
我有两个表Table1和Table2 ，在Table1的组成：
-
value 做个总结 -
Id 由分组 -
Code 持有外键，以Table2
和Table2由
我正在试图做的是，通过集团Table1.Id ，全面加入对Table2.Code ，但是，对于每个结果组，我想告诉所有从表2的行，由查询生成每个组。
示例代码：
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和值的所有组合，然后使用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
文章来源: Full Join on Group
