如何以某種方式使用 GROUP BY 連接一列中的數據，但過濾另一列中的特定數據

我正在執行 SQL Server 2014

我有一個看起來像這樣的表：

ID   | Name | AddressType | Address       | Features
========================================================
1    | Bob  | Home        | 123 Nope St   | JP 
2    | John | Work        | 555 Fake St   | MNGF 
2    | John | Home        | 654 Madeup Ln | IMP JP 
3    | Kim  | Work        | 92 Nadda Blvd | MP 

我正在嘗試編寫一個查找重複 ID 並始終返回包含“工作”地址類型的行的 SQL Server 查詢，但我希望它在“主”地址類型的功能中連接起來，以便我得到如下內容：

ID   | Name | AddressType | Address       | Features
========================================================
1    | Bob  | Home        | 123 Nope St   | JP 
2    | John | Work        | 555 Fake St   | MNGF IMP JP 
3    | Kim  | Work        | 92 Nadda Blvd | MP 

我能夠弄清楚如何得到的最接近的看起來是這樣的：

SELECT ID, Name, MAX(AddressType), MAX(Address), CONCAT(Features) FROM 
 (SELECT * FROM myTable ORDER BY ID, AddressType DESC)
GROUP BY ID, NAME

…但是 MAX(AddressType) 和 MAX(Address) 分別被拉出，所以有時我得到工作地址，有時我得到家庭地址。我真正需要的是 MAX(AddressType) 以及與該結果在同一行的任何地址。

我嘗試過的另一件事是：

SELECT ID, Name, AddressType, Address, CONCAT(Features), COUNT(ID) OVER(PARTITION BY ID) AS IDcount
FROM myTable 
GROUP BY ID, NAME
WHERE AddressType = 'Work' OR IDcount = 1

…這總是給我所有正確的地址，但會過濾掉“家庭”地址類型的功能，這樣我就無法將它們連接起來。

我以以下方式解決了您的問題-我既沒有使用視窗函式也沒有使用CTE-s，以防萬一您正在執行 SQL Server 的古董版本-無論如何，我發現這是一個有趣的智力練習（+1 發人深省問題）。

兩點：

• 將來，您能否提供一個工作小提琴（在dbfiddle.uk或其他地方）a）以便有一個單一的事實來源和 b）因此那些試圖回答您的問題的人不會重複努力 - 幫助我們來幫助你！
• 您也許應該考慮更改您的架構（如果可能的話，顯然，我意識到有些人在這件事上別無選擇 - 不聽的 PHB-s 或遺留問題） - 但是擁有一張address表將大大簡化您的 SQL（以及您的生活…）。

因此，我創建了一個測試表，如下所示（下面的所有 SQL 都可以在此處的小提琴中找到）：

CREATE TABLE test
(
 id           INTEGER      NOT NULL,
 name         VARCHAR (25) NOT NULL,
 address_type VARCHAR (25) NOT NULL,  
 address      VARCHAR (25) NOT NULL,
 features     VARCHAR (25) NOT NULL,
 
 CONSTRAINT at_h_w_ck CHECK (address_type IN ('Home', 'Work'))
);

並使用您的數據填充它：

INSERT INTO test VALUES
( 1, 'Bob',  'Home', '123 Nope St',   'JP'), 
( 2, 'John', 'Work', '555 Fake St',   'MNGF'), 
( 2, 'John', 'Home', '654 Madeup Ln', 'IMP JP'), 
( 3, 'Kim',  'Work', '92 Nadda Blvd', 'MP');

然後，我通過執行以下操作“模擬”了 ROW_COUNT() 函式：

SELECT t.*, tab1.cnt
FROM test t
JOIN
(
 SELECT id, COUNT(id) AS cnt
 FROM test 
 GROUP BY id
) AS tab1
ON t.id = tab1.id
WHERE t.address_type = 'Work' OR cnt = 1;

結果：

id  name  address_type address         features     cnt
1   Bob   Home         123 Nope St           JP     1
2   John  Work         555 Fake St         MNGF     2
3   Kim   Work         92 Nadda Blvd         MP     1

然後我像這樣提取John的記錄（唯一一個有 2 個地址的記錄）：

SELECT 
 tab2.id, tab2.name, tab2.address_type,
 CONCAT(tab2.features, ' ', t1.features) AS ft, cnt
FROM
(
 SELECT t.*, tab1.cnt
 FROM test t
 JOIN
 (
   SELECT id, COUNT(id) AS cnt
   FROM test 
   GROUP BY id
 ) AS tab1
 ON t.id = tab1.id
 WHERE t.address_type = 'Work' OR cnt = 1
) AS tab2
JOIN test t1
 ON tab2.id = t1.id AND tab2.address_type != t1.address_type;

結果：

id  name  address_type  ft            cnt
2  John  Work          MNGF IMP JP     2

因此，使用這個結果，我使用相同的 SQL創建了一個VIEW ：

CREATE VIEW my_ft_view AS
SELECT 
 tab2.id, tab2.name, tab2.address_type, tab2.address,
 CONCAT(tab2.features, ' ', t1.features) AS features
...
... snipped for brevity
...

然後執行這個查詢：

SELECT * FROM my_ft_view
UNION 
SELECT * FROM test WHERE id NOT IN (SELECT id FROM my_ft_view);

結果：

id  name  address_type  address            features
1   Bob  Home          123 Nope St              JP
2  John  Work          555 Fake St     MNGF IMP JP
3   Kim  Work           92 Nadda Blvd           MP

這是想要的結果！

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