时间戳

首页 > 解决方案 > 组合多个表时获取重复的主键（完全连接）

sql - 组合多个表时获取重复的主键（完全连接）

问题描述

我有 4 个表（每个代表一个月），只有 2 列（服务器和评级）。我只想对服务器名称进行分组，然后为每个月的评级设置一列。我不知道为什么要复制服务器。任何帮助将不胜感激！

目标是：

Server   | 1910 | 1911 | 1912 | 2001
---------+------+------+------+-----
abcds    | 2394 | Null | 2332 | 2332

代码：

SELECT 
    (CASE 
        WHEN A.[server] IS NOT NULL THEN A.[server] 
        ELSE (CASE 
                 WHEN B.[server] IS NOT NULL THEN B.[server] 
                 ELSE (CASE 
                          WHEN C.[server] IS NOT NULL THEN C.[server] 
                          ELSE (CASE 
                                   WHEN D.[server] IS NOT NULL THEN D.[server] 
                                   ELSE NULL 
                                END) 
                       END) 
              END) 
  END) AS Server, 
  D.[rating_configured] AS Rating_Config_1910, 
  C.[rating_configured] AS Rating_Config_1911, 
  B.[rating_configured] AS Rating_Config_1912, 
  A.[rating_configured] AS Rating_Config_2001, 
  (CASE 
      WHEN A.[rating_configured] IS NOT NULL THEN A.[rating_configured] 
      ELSE (CASE 
               WHEN B.[rating_configured] IS NOT NULL THEN B.[rating_configured]
               ELSE (CASE 
                        WHEN C.[rating_configured] IS NOT NULL THEN C.[rating_configured] 
                        ELSE (CASE 
                                 WHEN D.[rating_configured] IS NOT NULL 
                                    THEN D.[rating_configured] 
                                    ELSE NULL 
                              END) 
                    END) 
           END) 
     END) AS Most_Recent_Rating_Config 
FROM   
    [Jason_Orf].[dbo].[monthly_server_config_2001] A 
FULL JOIN 
    [dbo].[monthly_server_config_1912] B ON A.[server] = B.[server] 
FULL JOIN 
    [dbo].[monthly_server_config_1911] C ON A.[server] = C.[server] 
FULL JOIN  
    [dbo].[monthly_server_config_1910] D ON A.[server] = D.[server]

标签： sqlsql-server

解决方案

由于像这样的完全外连接最终会导致不太直观的行为，最好先server在子查询中收集所有相关值，然后将相关表左连接到该主列表：

SELECT servers.server
   , D.[rating_configured] AS Rating_Config_1910, 
   , C.[rating_configured] AS Rating_Config_1911, 
   , B.[rating_configured] AS Rating_Config_1912, 
   , A.[rating_configured] AS Rating_Config_2001, 
FROM (
    SELECT server FROM [monthly_server_config_2001]
    UNION SELECT server FROM [monthly_server_config_1912]
    UNION SELECT server FROM [monthly_server_config_1911]
    UNION SELECT server FROM [monthly_server_config_1910]
) AS servers
LEFT JOIN [monthly_server_config_2001] AS A ON servers.server = A.server
LEFT JOIN [monthly_server_config_1912] AS B ON servers.server = B.server
LEFT JOIN [monthly_server_config_1911] AS C ON servers.server = C.server
LEFT JOIN [monthly_server_config_1910] AS D ON servers.server = D.server
;

如果您仍然获得服务器名称的多个结果行，则服务器名称在这些表中的至少一个中不是唯一的。

在原始查询中使用 FULL OUTER JOIN 的问题在于，当 B 和 C 的服务器名称为“foo”但 A 没有“foo”时，B 和 C 会产生不同的结果行。使用以下查询最容易说明这一点：

SELECT *
FROM (VALUES ('A'), ('B')) AS A(server)
FULL JOIN (VALUES ('B'), ('C')) AS B(server) ON A.server = B.server
FULL JOIN (VALUES ('C'), ('D')) AS C(server) ON A.server = C.server
;

推荐阅读