Sql-Server
查找在另一列中有兩個值的組
給定以下範例行:
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
和的 RNOS
。然後加入您的表格以選擇行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:如果您的源表有大量行,您可能希望將
INTERSECT
and拆分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);