如何排除查詢中的非不同行

我有一個表，其中包含對受問題影響的客戶的引用。父表包含問題資訊，特別是 ProblemID (PbMID)。由於一個問題會影響多個客戶端，因此我們將受客戶端影響的數據儲存在子表中。子表包含一個用於內務處理的 ID 欄位、一個將外鍵返回到父表的 PbMID 欄位和一個包含客戶文本名稱的 Company 欄位。如果單個客戶受到影響，我需要解決所有問題。如果我使用 DISTINCT，我會得到所有單客戶端行，但我也會得到多客戶端問題的第一行，這不是我被要求的。

這是受客戶影響的表範例

ID  | PbMID | Company    | 
1   | 1     | Company 1  | Valid
2   | 4     | Company 2  | Valid
3   | 6     | Company 3  | Valid
4   | 22    | Company 1  | Invalid
5   | 22    | Company 4  | Invalid
6   | 23    | Company 5  | Valid
7   | 24    | Company 6  | Valid
8   | 25    | Company 1  | Invalid
9   | 25    | Company 8  | Invalid
10  | 25    | Company 10 | Invalid
11  | 26    | Company 2  | Valid
12  | 27    | Company 4  | Valid

將不包括標記為 INVALID 的行，因為它們反映了多客戶端問題。

因此，理想情況下，回報將是：

ID  | PbMID | Company    | 
1   | 1     | Company 1  | Valid
2   | 4     | Company 2  | Valid
3   | 6     | Company 3  | Valid
6   | 23    | Company 5  | Valid
7   | 24    | Company 6  | Valid
11  | 26    | Company 2  | Valid
12  | 27    | Company 4  | Valid

任何幫助將不勝感激。SQL 不是我的強項，所以我一直在努力解決這個問題，但沒有運氣。

您可以使用GROUP BY、HAVING和公用表表達式 (CTE) 來獲取數據。

GROUP BY 和 HAVING 提供了所有僅影響單個公司的 PbMID。如果您需要影響 n 家公司的 PbMID，您可以將 HAVING 更改為 HAVING =n，其中 n 是所需的公司數量。

SELECT
   PbMID
FROM
   ChildTable
GROUP BY PbMId
HAVING COUNT(Company) =1; 

然後可以將其與 CTE 結合以生成下面的最終查詢。

WITH CTE_SingleInstance (PbMIDSinglInstance)
AS
(
   SELECT
       PbMID
   FROM
       ChildTable
   GROUP BY PbMId
   HAVING COUNT(Company) =1    
)
SELECT
   ChildTable.ID,
   ChildTable.PbMId ,
   ChildTable.Company
FROM
   ChildTable CT
JOIN
   CTE_SingleInstance  CTES
ON
   CT.PbMId  = CTES.PbMIDSinglInstance;

我相信這會讓你得到你所追求的;-)

WITH ProblemTable AS (
SELECT 1  AS ID, 1   AS PbMID,'Company 1 ' AS Company
UNION ALL SELECT 2  AS ID, 4   AS PbMID,'Company 2 ' AS Company
UNION ALL SELECT 3  AS ID, 6   AS PbMID,'Company 3 ' AS Company
UNION ALL SELECT 4  AS ID, 22  AS PbMID,'Company 1 ' AS Company
UNION ALL SELECT 5  AS ID, 22  AS PbMID,'Company 4 ' AS Company
UNION ALL SELECT 6  AS ID, 23  AS PbMID,'Company 5 ' AS Company
UNION ALL SELECT 7  AS ID, 24  AS PbMID,'Company 6 ' AS Company
UNION ALL SELECT 8  AS ID, 25  AS PbMID,'Company 1 ' AS Company
UNION ALL SELECT 9  AS ID, 25  AS PbMID,'Company 8 ' AS Company
UNION ALL SELECT 10 AS ID, 25  AS PbMID,'Company 10' AS Company
UNION ALL SELECT 11 AS ID, 26  AS PbMID,'Company 2 ' AS Company
UNION ALL SELECT 12 AS ID, 27  AS PbMID,'Company 4 ' AS Company
)

SELECT  p.ID
       ,p.PbMID
       ,p.Company
FROM    ProblemTable p
       CROSS APPLY (
                       SELECT  COUNT(1) AS ProblemCount
                       FROM    ProblemTable p2
                       WHERE   p.PbMID = p2.PbMID
                   ) pc
WHERE   1 = 1
       AND pc.ProblemCount = 1

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