SQL查询以标识适用于一组不同数据的记录

Question

我有以下查询，它返回结果，如下所示。然而，我只需要这些数据的一个子集。我感兴趣的是获取仅与RegionID值2相关联而不与任何其他区域相关联的DocumentID。

SELECT D.DocumentID, R.RegionID, COUNT(*) AS NUMOFPLANTSBYREGION 
FROM Document D INNER JOIN ShopAreaDoc SAD ON D.DocumentID = SAD.DocumentID 
INNER JOIN PlantShopAreaDoc PSAD ON SAD.ShopAreaDocID = PSAD.ShopAreaDocID 
INNER JOIN Plant P ON PSAD.PlantID = P.PlantID 
INNER JOIN Region R ON P.RegionID = R.RegionID 
GROUP BY D.DocumentID, R.RegionID 
ORDER BY D.DocumentID 

查询结果：

+------------+----------+---------------------+
| DocumentID | RegionID | NUMOFPLANTSBYREGION |
+------------+----------+---------------------+
|       2126 |        2 |                   8 |
|       2127 |        2 |                   8 |
|       2128 |        2 |                   8 |
|       2129 |        2 |                   8 |
|       2130 |        2 |                   8 |
|       2134 |        4 |                  13 |
|       2135 |        3 |                   8 |
|       2136 |        6 |                   9 |
|       2137 |        2 |                   8 |
|       2138 |        3 |                   8 |
|       2138 |        1 |                  20 |
|       2138 |        6 |                   9 |
|       2138 |        4 |                  14 |
|       2138 |        2 |                   8 |
|       2139 |        1 |                  17 |
|       2140 |        1 |                  17 |
+------------+----------+---------------------+

我感兴趣的结果如下：

其他记录要么不适用于2的区域ID，要么适用于除2以外的更多区域，因此应排除。

+------------+----------+----------------------+
| DocumentID | RegionID | NUMOFPLANTSBYREGION  |
+------------+----------+----------------------+
|       2126 |        2 |                    8 |
|       2127 |        2 |                    8 |
|       2128 |        2 |                    8 |
|       2129 |        2 |                    8 |
|       2130 |        2 |                    8 |
|       2137 |        2 |                    8 |
+------------+----------+----------------------+

Answer 1

使用公共表表达式和not exists()

;with cte as (
  SELECT D.DocumentID, R.RegionID, COUNT(*) AS NUMOFPLANTSBYREGION 
  FROM Document D INNER JOIN ShopAreaDoc SAD ON D.DocumentID = SAD.DocumentID 
  INNER JOIN PlantShopAreaDoc PSAD ON SAD.ShopAreaDocID = PSAD.ShopAreaDocID 
  INNER JOIN Plant P ON PSAD.PlantID = P.PlantID 
  INNER JOIN Region R ON P.RegionID = R.RegionID
  GROUP BY D.DocumentID, R.RegionID 
)
select *
from cte
where not exists (
  select 1
  from cte i
  where i.DocumentID = cte.DocumentID
    and i.RegionID <> 2
  )

rextester演示：http://rextester.com/DUIE27467

返回：

+------------+----------+----------------------+
| DocumentID | RegionID | NUMOFPLANTSBYREGION  |
+------------+----------+----------------------+
|       2126 |        2 |                    8 |
|       2127 |        2 |                    8 |
|       2128 |        2 |                    8 |
|       2129 |        2 |                    8 |
|       2130 |        2 |                    8 |
|       2137 |        2 |                    8 |
+------------+----------+----------------------+

Answer 2

  SELECT D.DocumentID, R.RegionID, COUNT(*) AS NUMOFPLANTSBYREGION 
    FROM Document D 
    INNER JOIN ShopAreaDoc SAD ON D.DocumentID = SAD.DocumentID 
    INNER JOIN PlantShopAreaDoc PSAD ON SAD.ShopAreaDocID = PSAD.ShopAreaDocID 
    INNER JOIN Plant P ON PSAD.PlantID = P.PlantID 
    INNER JOIN Region R ON P.RegionID = R.RegionID 
    WHERE R.RegionId = 2
    AND NOT EXISTS (SELECT * FROM Region R2 WHERE R2.RegionId <> 2 AND R2.DocumentId = D.documentid)
    GROUP BY D.DocumentID, R.RegionID 
    ORDER BY D.DocumentID 

Answer 3

您可以使用这种方法，实现减号语句(顺便说一下，在MySQL中不存在)：

SELECT DocumentID FROM docs WHERE RegionID=2 AND DocumentID NOT IN (SELECT DocumentID FROM docs WHERE RegionID<>2)

我也在http://sqlfiddle.com/#!9/66f0e8/57上试过了，看起来很管用。