如何合併這兩個查詢以使結果集“合併”且一致？

請考慮我的第一個查詢：

SELECT cf.cnum as cnum, rc.name as name, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
INNER JOIN research..complex rc
ON cf.cnum = rc.cnum
WHERE rs.date  = '9/30/2016'
GROUP BY rc.name, cf.cnum
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0
ORDER BY cf.cnum

這給了我以下結果集：

這是我的第二個查詢：

SELECT r.cnum as cnum, c.name as name, r.assets as assets
FROM research..complex c, research..retirement_simple_ira r
where r.date = '9/30/2016'
and c.cnum = r.cnum
ORDER BY cnum

這給了我以下結果集：

我一直在嘗試很多不同的變體並將相關表連接在一起，但我無法獲得一致的結果。我最終想要的是一個包含四列的結果集：

數字 | 姓名 | 總和| 資產

請幫我解決一下這個。

有點懶惰，最簡單的處理方法是使用 aFULL OUTER JOIN和子查詢。我假設這是 SQL Server 順便說一句。

SELECT ISNULL(x.cnum, y.cnum) cnum, ISNULL(x.name, y.name) name,
   x.sums, y.assets
(
   SELECT cf.cnum as cnum, rc.name as name, SUM(rs.dh_simple + rs.ha_simple) as sums
   FROM retirement_survey as rs
   INNER JOIN currentfunds as cf
   ON rs.fundid = cf.fundid
   INNER JOIN research..complex rc
   ON cf.cnum = rc.cnum
   WHERE rs.date  = '9/30/2016'
   GROUP BY rc.name, cf.cnum
   HAVING SUM(rs.dh_simple + rs.ha_simple) > 0
) x
FULL OUTER JOIN
(
   SELECT r.cnum as cnum, c.name as name, r.assets as assets
   FROM research..complex c, research..retirement_simple_ira r
   where r.date = '9/30/2016'
   and c.cnum = r.cnum
) y
   ON x.cnum = r.cnum
ORDER BY ISNULL(x.cnum, y.cnum)

假設您的表是按以下方式定義的（我做了一些小的命名更改，並假設了很多事情）：

CREATE TABLE current_funds
(
    fundid integer PRIMARY KEY,
    cnum integer NOT NULL,
    UNIQUE (cnum)
) ;

CREATE TABLE retirement_survey
(
   fundid integer NOT NULL REFERENCES current_funds (fundid),
   "date" date,
   dh_simple integer NOT NULL,
   ha_simple integer DEFAULT 0
) ;

CREATE TABLE research_complex
(
    cnum integer REFERENCES current_funds (cnum),
    name varchar(50)
) ;

CREATE TABLE research_retirement_simple_ira
(
   cnum integer REFERENCES current_funds (cnum),
   "date" date,
   assets integer
) ;

通過一些細微的命名更改，以下查詢應該適合您：

SELECT
   coalesce(s1.cnum, s2.cnum) AS cnum, 
   coalesce(s1.name, s2.name) AS name, 
   s1.sums, 
   s2.assets
FROM
   (SELECT 
       cf.cnum as cnum, 
       rc.name as name, 
       SUM(rs.dh_simple + rs.ha_simple) as sums
   FROM 
       retirement_survey as rs
       INNER JOIN current_funds as cf ON rs.fundid = cf.fundid
       INNER JOIN research_complex rc ON cf.cnum = rc.cnum
   WHERE 
       rs.date  = '20160930'
   GROUP BY 
       rc.name, cf.cnum
   HAVING 
       SUM(rs.dh_simple + rs.ha_simple) > 0
   ) AS s1
   FULL OUTER JOIN
   (SELECT 
       rc.cnum as cnum, 
       rc.name as name, 
       r.assets as assets
   FROM 
       research_complex rc
       JOIN research_retirement_simple_ira r ON r.cnum = rc.cnum
   WHERE 
       r.date = '20160930'
   ) s2
   ON s1.cnum = s2.cnum 
ORDER BY 
   cnum ;

並給你以下結果：

數字 | 姓名 | 總和| 資產
---: | :------------------------------------------ | ------: | ---------:
2 | Lord, Abbett & Co. LLC | 329929 | 303026683
7 | 資本、研究與管理 | *空*| 20000000
14 | 校長 | 867280 | 777135509
15 | 橋樑 | 430 | *空*
35 | 特拉華投資 | 147950 | *空*
38 | 紐約梅隆銀行/Dreyfus Corporation | 1417 | *空*
43 | 紐伯格伯曼管理有限責任公司 | 14273 | *空*
45 | 聯邦投資者 | *空*| 67583161
46 | 富達投資 | 7676456 | 20000000
47 | 林業金融 | 78766 | 9999
49 | 卡爾弗特投資 | 76591 | *空*
51 | 富蘭克林鄧普頓投資 | *空*| 1848513179
55 | 威廉布萊爾基金 | 233 | *空值*
59 | Columbia Threadneedle 投資 | 138908 | 138678936
68 | MFS 投資管理 | 1551971 | 1518052790
71 | 全國基金 | 10183 | *空值*
80 | 奧本海默基金/MassMutual | 4101977 | 20000000
85 | 先鋒投資管理美國公司| *空*| 289936684
88 | T. Rowe 價格 | 1905810 | 1921051937
91 | 普特南基金 | 169569 | 20000000
102 | 國營農場 | 999378 | 1026211854
110 | Waddell & Reed 基金 | 700310 | 560233847
115 | 先鋒集團 | 6938415 | 20000000
130 | 美國航空航天局 | 169139 | 168430000
143 | 景順 | 1822865 | 1798640460
149 | 附屬經理組 | 102 | *空*
153 | 高盛公司| 182038 | *空*
156 | TCW 基金公司 | 3 | *空*
169 | 戴維斯精選顧問，LP | 16633 | *空*
200 | 德意志資產管理 | 236911 | *空值*
207 | 哈特福德基金 | 1047561 | 586878824
226 | Meeder 資產管理公司 | 第1487章 *空*
236 | 紐文投資 | 33764 | *空*
275 | SunAmerica 集團 | 69726 | *空*
280 | 劍鋒 | 1899 | *空*
286 | 萊格梅森 | 5533 | *空*
302 | 泛美資產管理 | 308727 | *空*
304 | 約翰漢考克投資 | *空*| 342692928
379 | 摩根大通公司| *空*| 148135910
530 | 施瓦布基金、勞德斯基金和施瓦布 ETF | *空*| 20000000

您可以在dbfiddle)檢查所有設置（檢查 SQL Server 設置，這些設置非常接近 Sybase；它也適用於 PostgreSQL 設置）。

注意：這基本上與@kenneth-fisher 採用的方法相同……只是增加了概念證明。

---

如果我對錶結構的假設是正確的，則以下查詢將返回相同的結果並且效率更高：

SELECT
   s3.cnum, rc.name, s3.sums, s3.assets
FROM
   (SELECT
       coalesce(s1.cnum, s2.cnum) AS cnum, s1.sums, s2.assets
   FROM
       (SELECT 
           cf.cnum as cnum, 
           SUM(rs.dh_simple + rs.ha_simple) as sums
       FROM 
           retirement_survey as rs
           INNER JOIN current_funds as cf ON rs.fundid = cf.fundid
       WHERE 
           rs.date  = '20160930'
       GROUP BY 
           cf.cnum
       HAVING 
           SUM(rs.dh_simple + rs.ha_simple) > 0
       ) AS s1
       FULL OUTER JOIN
       (SELECT 
           rc.cnum as cnum, 
           r.assets as assets
       FROM 
           research_retirement_simple_ira
       WHERE 
           r.date = '20160930'
       ) s2
       ON s1.cnum = s2.cnum 
   ) s3
   JOIN research_complex rc ON rc.cnum = s3.cnum
ORDER BY 
   cnum ;

也在dbfiddle檢查它。

引用自：https://dba.stackexchange.com/questions/173283