连接3个表并从所有表中检索所有记录 [英] Joining 3 tables and retrieve all the records from all the tables
问题描述
我正在联接三个表(执行完全外部联接),以便可以从所有表中检索所有记录.我面临的问题是联接表的顺序.
I am joining three tables (performing a full outer join) so that I can retrieve all the records from all the tables. Problem that I am facing is with the order in which I join tables.
表格信息
替代文字http://img235.imageshack.us/img235/7980/tableinfoow1 .png
(1)如果我以TABLE1,TABLE2,TABLE3顺序连接表,我将获得两行记录,以记录团队B和级别1.
(1) If I join tables in TABLE1, TABLE2, TABLE3 sequence I get two rows for record with team B and Level 1.
SELECT DISTINCT
(CASE WHEN T0.[TEAM] IS NOT NULL THEN T0.[TEAM] WHEN T1.[TEAM] IS NOT NULL THEN T1.[TEAM] WHEN T2.[TEAM] IS NOT NULL THEN T2.[TEAM] ELSE T0.[TEAM] END) AS [TEAM],
(CASE WHEN T0.[LEVEL] IS NOT NULL THEN T0.[LEVEL] WHEN T1.[LEVEL] IS NOT NULL THEN T1.[LEVEL] WHEN T2.[LEVEL] IS NOT NULL THEN T2.[LEVEL] ELSE T0.[LEVEL] END) AS [LEVEL],
T0.[VALUE1] AS [VALUE1],
T1.[VALUE2] AS [VALUE2],
T2.[VALUE3] AS [VALUE3]
FROM TABLE1 T0
FULL JOIN TABLE2 T1 ON T0.[TEAM] = T1.[TEAM] AND T0.[LEVEL] = T1.[LEVEL]
FULL JOIN TABLE3 T2 ON T0.[TEAM] = T2.[TEAM] AND T0.[LEVEL] = T2.[LEVEL]
(2)如果我以TABLE2,TABLE3,TABLE1顺序连接表,则输出中的行数正确.
(2) If I join tables in TABLE2, TABLE3, TABLE1 sequence I get correct number of rows in the output.
SELECT DISTINCT
(CASE WHEN T0.[TEAM] IS NOT NULL THEN T0.[TEAM] WHEN T1.[TEAM] IS NOT NULL THEN T1.[TEAM] WHEN T2.[TEAM] IS NOT NULL THEN T2.[TEAM] ELSE T0.[TEAM] END) AS [TEAM],
(CASE WHEN T0.[LEVEL] IS NOT NULL THEN T0.[LEVEL] WHEN T1.[LEVEL] IS NOT NULL THEN T1.[LEVEL] WHEN T2.[LEVEL] IS NOT NULL THEN T2.[LEVEL] ELSE T0.[LEVEL] END) AS [LEVEL],
T0.[VALUE1] AS [VALUE1],
T1.[VALUE2] AS [VALUE2],
T2.[VALUE3] AS [VALUE3]
FROM TABLE2 T0
FULL JOIN TABLE3 T1 ON T0.[TEAM] = T1.[TEAM] AND T0.[LEVEL] = T1.[LEVEL]
FULL JOIN TABLE1 T2 ON T0.[TEAM] = T2.[TEAM] AND T0.[LEVEL] = T2.[LEVEL]
我面临的问题是我不知道输入表,并在运行时将所有这些表作为用户的输入并执行联接.我不能一次合并两个表,因为我的表一次可以合并三个以上的表(最多9个或10个).
Problem I am facing is that I am not aware of the input tables and take all these tables as an input from user at runtime and perform a join. I cannot merge two tables at a time since my table can technically merge more than three tables at a time (upto 9 or 10).
如何确保我从所有表中获取所有记录(使用完全外部联接),但不能像#1中那样获得两行.
How can I ensure that I get all records from all tables (using full outer join) but DO not get two rows as in #1.
推荐答案
如果这是您需要的:
TEAM LEVEL Value1 Value2 Value3
A 1 1 NULL NULL
B 1 NULL 1000 900
然后您可以通过以下方法实现这一目标:
Then you can achieve that with the following:
SELECT [TEAM], [LEVEL], MAX(v1) Value1, MAX(v2) Value2, MAX(v3) Value3
FROM (
SELECT [TEAM], [LEVEL], Value1 v1, NULL v2, NULL v3
FROM TABLE1
UNION
SELECT [TEAM], [LEVEL], NULL, Value2, NULL
FROM TABLE2
UNION
SELECT [TEAM], [LEVEL], NULL, NULL, Value3
FROM TABLE3
) t0
GROUP BY [TEAM], [LEVEL]
,您可以根据需要使用任意数量的表.
and you can use as many tables as you need.
这篇关于连接3个表并从所有表中检索所有记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!