查找在另一列中有兩個值的組

給定以下範例行：

MTYPE RNO VAL
S     1   V1
S     1   V2
S     1   V3
R     1   V4
S     2   V5
S     2   V6
R     3   V7
R     3   V8

如何列出在 MTYPE 列的任何行中同時具有“R”和“S”的 RNO 組。在上面的範例數據中，結果應該是：

MTYPE RNO VAL
S     1   V1
S     1   V2
S     1   V3
R     1   V4

由於 RNO=1 組在 MTYPE 列中同時具有 S 和 R 行。

首先，找到同時具有R和的 RNO S。然後加入您的表格以選擇行

with
rno as
(
   select  RNO
   from    yourtable 
   group by RNO
   having  min(MTYPE) = 'R'
   and     max(MTYPE) = 'S'
)
select  *
from    rno  r
       inner join yourtable t  on  r.RNO   = t.RNO

我建議不要使用聚合，而是使用INTERSECT，如下所示：

;WITH src AS
(
   SELECT t.RNO
   FROM #t t
   WHERE t.MTYPE = 'S'
   INTERSECT
   SELECT t.RNO
   FROM #t t
   WHERE t.MTYPE = 'R'
)
SELECT t.*
FROM #t t
   INNER JOIN src ON t.RNO = src.RNO;

如果您使用MIN()andMAX()來確定包含所需值的行的交集，您將看到不正確的結果如果源表中的行包含在 column\table\database 使用的特定排序規則之前R或之後出現的值S，則查詢將返回不正確的結果。

作為一個例子，考慮這個最小的、完整的和可驗證的例子：

DROP TABLE IF EXISTS #t;

CREATE TABLE #t
(
   MTYPE   char(1) NOT NULL
   , RNO   tinyint NOT NULL
   , VAL   char(2) NOT NULL
);

INSERT INTO #t (MTYPE, RNO, VAL)
VALUES 
     ('Q', 1, 'V1')
   , ('S', 1, 'V2')
   , ('S', 1, 'V3')
   , ('R', 1, 'V4')
   , ('S', 2, 'V5')
   , ('S', 2, 'V6')
   , ('R', 3, 'V7')
   , ('T', 3, 'V8');


;with
rno as
(
   select  RNO
   from    #t
   group by RNO
   having  min(MTYPE) = 'R'
   and     max(MTYPE) = 'S'
)
select  *
from    rno  r
       inner join #t t  on  r.RNO   = t.RNO;

輸出返回零行：

而INTERSECT運算符返回任何同時具有S和 RMTYPE值的 RNO：

如果您的源表有大量行，您可能希望將INTERSECTand拆分SELECT為兩個操作：

DROP TABLE IF EXISTS #x;

SELECT t.RNO
INTO #x
FROM #t t
WHERE t.MTYPE = 'S'
INTERSECT
SELECT t.RNO
FROM #t t
WHERE t.MTYPE = 'R'

SELECT *
FROM #x
   INNER JOIN #t ON #x.RNO = #t.RNO;

我還考慮將以下索引添加到表中：

CREATE INDEX t_MTYPE
ON #t (MTYPE, RNO)
INCLUDE (VAL);

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