SQL数据库 数据筛选求助

[ysycysyc]

表TBM 每条记录的6个字段不重复，想通过2组数据对TBM的记录进行筛选，筛选出满足以下条件的记录：
要么不包含这2组数据中的任何一个数，如果包含 则这2组数据任意1组的个数得超过3个，即：不要1~3个的记录。
数据如下，请大虾帮忙指点：

筛选数据：

第1组：--3,5,7,9,10,11,20,300

第2组：--1,3,5,7,10,20,30,40,100,65

待筛选数据：
CREATE TABLE TBM (A INT,B INT,C INT,D INT,E INT, F INT)

INSERT INTO TBM (A,B,C,D,E,F) VALUES 
(1,2,3,10,11,20),  --满足条件：包含第1组数中的3,10,11,20，超过3个
(1,2,3,4,5,7),  --满足条件：包含第2组数中的1,3,5,7，超过3个
(1,2,3,4,5,100),  --满足条件：包含第2组数中的1,3,5,100，超过3个
(3,5,7,100,300,65),  --满足条件：包含第1组数中的3,5,7,300，包含第2组数中的3,5,7,100,65,均超过3个
(2,4,6,8,12,14),  --满足条件：不包含2组数
(1,20,30,90,50,60),
(200,20,30,40,50,70),
(300,20,30,40,50,80),
(400,20,30,40,50,90),
(100,200,300,40,500,60),
(100,200,300,40,500,70),
(100,200,300,40,500,80),
(100,200,300,40,500,90)

[河狸吗]

DECLARE @TBM TABLE(A INT,B INT,C INT,D INT,E INT, F INT)

INSERT INTO @TBM (A,B,C,D,E,F) VALUES 
(1,2,3,10,11,20),  
(1,2,3,4,5,7), 
(1,2,3,4,5,100),  
(3,5,7,100,300,65), 
(2,4,6,8,12,14),
(1,20,30,90,50,60),
(200,20,30,40,50,70),
(300,20,30,40,50,80),
(400,20,30,40,50,90),
(100,200,300,40,500,60),
(100,200,300,40,500,70),
(100,200,300,40,500,80),
(100,200,300,40,500,90)
 

DECLARE @Test1 TABLE (R1 INT ,Val INT)
INSERT INTO @Test1 ( R1, Val )
VALUES (1,3),(1,5),(1,7),(1,9),(1,10),(1,11),(1,20),(1,300),
(2,1),(2,3),(2,5),(2,7),(2,10),(2,20),(2,30),(2,40),(2,100),(2,65)

;WITH tempA AS (
    SELECT ROW_NUMBER() OVER (ORDER BY A) AS R2, * FROM @TBM
) 
,tempB AS (--将TBM列转行，再去关联筛选的数据
    SELECT tb2.R2,COUNT(tb2.R2) AS R2Count ,tb1.R1,COUNT(R1) AS R1Count
    FROM tempA AS a UNPIVOT( Val FOR Cols IN ([A],[B],[C],[D],[E],[F])) AS tb2
    LEFT JOIN @Test1 AS tb1
    ON tb1.Val = tb2.VAL
    GROUP BY R2,R1 
)
SELECT DISTINCT R2 FROM tempB
WHERE R2Count = 6 OR R1Count > 3
 

[ysycysyc]

非常感谢大虾帮助指点，我学习研究一下。

[ysycysyc]

@河狸吗 非常感谢大虾，数据库是自己学着玩的，基础太弱，很多地方不会 > 这个是不是在SQL2008里不能用，我改成 > 了。

[河狸吗]

@河狸吗 ;WITH tempA AS ( SELECT ROW_NUMBER() OVER (ORDER BY A) AS R2, * FROM @TBM ) ,tempB AS (--将TBM列转行，再去关联筛选的数据 SELECT tb2.R2,COUNT(tb2.R2) AS R2Count ,tb1.R1,COUNT(R1) AS R1Count FROM tempA AS a UNPIVOT( Val FOR Cols IN ([A],[B],[C],[D],[E],[F])) AS tb2 LEFT JOIN @Test1 AS tb1 ON tb1.Val = tb2.VAL GROUP BY R2,R1 ) SELECT DISTINCT ta.* FROM tempB AS tb JOIN tempA AS ta ON ta.R2 = tb.R2 WHERE R2Count = 6 OR R1Count > 3

[河狸吗]

@ysycysyc 你再join 一下tempA 就出结果了，我没完全写完，我以为到这步你自己能解决了

[ysycysyc]

大虾，好像没成功，运行后没显示那5条满足条件的记录。