gpt4 book ai didi

sql - 加入 3 个表并从所有表中检索所有记录

转载 作者:搜寻专家 更新时间:2023-10-30 20:22:30 25 4
gpt4 key购买 nike

我正在连接三个表(执行完全外部连接),以便我可以从所有表中检索所有记录。我面临的问题是我加入表格的顺序。

表信息

alt text http://img235.imageshack.us/img235/7980/tableinfoow1.png

(1) 如果我按 TABLE1、TABLE2、TABLE3 顺序连接表,我会得到两行用于记录团队 B 和级别 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 顺序连接表,我会在输出中得到正确的行数。

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 个)。

我如何确保从所有表中获取所有记录(使用完全外部联接)但不要像#1 中那样获取两行。

最佳答案

如果这是你需要的:

TEAM LEVEL  Value1  Value2  Value3
A 1 1 NULL NULL
B 1 NULL 1000 900

然后您可以通过以下方式实现:

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]

您可以根据需要使用任意数量的表格。

关于sql - 加入 3 个表并从所有表中检索所有记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/441939/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com