本文介绍了完全加入群组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的查询遇到逻辑问题.
我有两个表 Table1 和 Table2 ，其中Table1包含:
-
value总和 -
Id要归类于 -
Code持有Table2的外键
Table2由
-
Code -
Des代码的文本描述
我想做的是，按Table1.Id分组，在Table2.Code上进行完全联接，但是，对于每个结果组，我想显示查询所生成的每个组的Table2中的所有行. /p>
示例代码:
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
解决方案
您可以通过某种方式显示值对(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
在val中使用
UNION ALL，因为可能会出现重复.
不需要FULL OUTER JOIN.
I'm facing a logic issue with my Query.
I have two tables Table1 and Table2, where Table1 consists of:
valueto be summedIdto be grouped byCodeholds foreign-key toTable2
And Table2 consists of
CodeDesthe text description of code
What I'm trying to do is, group by Table1.Id, full join on Table2.Code, but, for each resulting group, I want to show all the rows from Table2 for each group generated by the query.
Sample code:
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
Result:
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
Required Result:
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
解决方案
You have somehow to show the value pair (D,2) eg. by making a code list with possible values and translating NULL to 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 is used in val because duplicates can occur.
No need for FULL OUTER JOIN.
这篇关于完全加入群组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！