如何遍历查询表中每行数据后将符合条件的结果统计后反馈

[wx9288]

有两个数据表
A表存有基础数据信息

ID aa bb cc dd ee
A1 1 2 3 4 5
A2 1 3 4 5 6
A3 3 4 5 6 7
A4 2 3 7 11 12
A4 2 3 4 8 9
....
然后B表有给定几组数据
ID X1 X2 X3 X4 X5 A表中相同4个数据有几个
B1 1 2 3 4 9 ？？
B2 1 3 4 5 6 ？？
....

现想通过B表给定的数据在A表查询，如果B表每行中有4个数据 在A表每行检索中存在，就累计加1，最后将结果反馈到B表
如B表中ID B2 的 “1 3 4 5 6 ”其中 3 4 5 6
这4个数刚好在A表中ID A2 和 ID A3 都有找到4个数跟他相同，则累计加1

B表结果为
ID X1 X2 X3 X4 X5 A表中相同4个数据有几个
B1 1 2 3 4 9 2 // (因为a1有1234 a4有2349)
B2 1 3 4 5 6 2 // (因为a2有1345 a3有3456)

[RINK_1]

a1和B2为啥不算一对

 CREATE TABLE #T1
 (ID VARCHAR(10),
  AA INT,
  BB INT,
  CC INT,
  DD INT,
  EE INT)

INSERT INTO #T1
SELECT 'A1',1,2,3,4,5 UNION ALL
SELECT 'A2',1,3,4,6,5 UNION ALL
SELECT 'A3',3,4,5,6,7 UNION ALL
SELECT 'A4',2,3,7,11,12 UNION ALL
SELECT 'A5',2,3,4,8,9 

CREATE TABLE #T2
(ID VARCHAR(10),
 X1 INT,
 X2 INT,
 X3 INT,
 X4 INT,
 X5 INT)

INSERT INTO #T2
SELECT 'B1',1,2,3,4,9 UNION ALL
SELECT 'B2',1,3,4,5,6

WITH CTE_1
AS
(SELECT ID,AA AS VALUE_T1 FROM #T1
 UNION ALL
 SELECT ID,BB FROM #T1
 UNION ALL
 SELECT ID,CC FROM #T1
 UNION ALL
 SELECT ID,DD FROM #T1
 UNION ALL
 SELECT ID,EE FROM #T1),

CTE_2
AS
(SELECT ID,X1 AS VALUE_T2 FROM #T2
 UNION ALL
 SELECT ID,X2 FROM #T2
 UNION ALL
 SELECT ID,X3 FROM #T2
 UNION ALL
 SELECT ID,X4 FROM #T2
 UNION ALL
 SELECT ID,X5 FROM #T2)

 SELECT ID_B,COUNT(*) AS AMOUNT_MATCH
 FROM
 (SELECT ID_A,ID_B,COUNT(*) AS AMOUNT 
 FROM (SELECT DISTINCT ID AS ID_A FROM #T1) AS A
 JOIN (SELECT DISTINCT ID AS ID_B FROM #T2) AS B ON 1=1
 JOIN CTE_1 C ON A.ID_A=C.ID
 JOIN CTE_2 D ON B.ID_B=D.ID AND C.VALUE_T1=D.VALUE_T2
 GROUP BY ID_A,ID_B
 HAVING COUNT(*)>=4) AS E
 GROUP BY ID_B

[wx9288]

引用 1 楼 RINK_1 的回复:

a1和B2为啥不算一对

 CREATE TABLE #T1
 (ID VARCHAR(10),
  AA INT,
  BB INT,
  CC INT,
  DD INT,
  EE INT)

INSERT INTO #T1
SELECT 'A1',1,2,3,4,5 UNION ALL
SELECT 'A2',1,3,4,6,5 UNION ALL
SELECT 'A3',3,4,5,6,7 UNION ALL
SELECT 'A4',2,3,7,11,12 UNION ALL
SELECT 'A5',2,3,4,8,9 

CREATE TABLE #T2
(ID VARCHAR(10),
 X1 INT,
 X2 INT,
 X3 INT,
 X4 INT,
 X5 INT)

INSERT INTO #T2
SELECT 'B1',1,2,3,4,9 UNION ALL
SELECT 'B2',1,3,4,5,6

WITH CTE_1
AS
(SELECT ID,AA AS VALUE_T1 FROM #T1
 UNION ALL
 SELECT ID,BB FROM #T1
 UNION ALL
 SELECT ID,CC FROM #T1
 UNION ALL
 SELECT ID,DD FROM #T1
 UNION ALL
 SELECT ID,EE FROM #T1),

CTE_2
AS
(SELECT ID,X1 AS VALUE_T2 FROM #T2
 UNION ALL
 SELECT ID,X2 FROM #T2
 UNION ALL
 SELECT ID,X3 FROM #T2
 UNION ALL
 SELECT ID,X4 FROM #T2
 UNION ALL
 SELECT ID,X5 FROM #T2)

 SELECT ID_B,COUNT(*) AS AMOUNT_MATCH
 FROM
 (SELECT ID_A,ID_B,COUNT(*) AS AMOUNT 
 FROM (SELECT DISTINCT ID AS ID_A FROM #T1) AS A
 JOIN (SELECT DISTINCT ID AS ID_B FROM #T2) AS B ON 1=1
 JOIN CTE_1 C ON A.ID_A=C.ID
 JOIN CTE_2 D ON B.ID_B=D.ID AND C.VALUE_T1=D.VALUE_T2
 GROUP BY ID_A,ID_B
 HAVING COUNT(*)>=4) AS E
 GROUP BY ID_B

是算一对的，我之前的疏忽 能解决此问题