A table containing basic data information
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 and 9
.
Then B table has given several groups of data
ID X1 X2 X3 X4 X5 same four data in A table A few
B1 (1, 2, 3, 4, 9??
B2 1, 3, 4, 5, 6??
.
Now want to pass the B table given data in A table query, if B table has four data in each row in A table row retrieval, cumulative add 1, finally the result feedback to table B
Such as ID in the table B2 B "1, 3, 4, 5 and 6" among them, 3, 4, 5 and 6
The four number in A table ID just A2 and A3 ID has found four number is the same with him, the cumulative add 1
B table results for
ID X1 X2 X3 X4 X5 same four data in A table A few
B1 (1, 2, 3, 4, 9 2//(because a1 1234 a4 2349)
1, 3, 4, 5 6 2//B2 (because a2 1345 a3 3456)
CodePudding user response:
Why not a pair ofa1 and B2
# the 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 the A5, 2,3,4,8,9
The CREATE TABLE # T2
(ID VARCHAR (10),
The X1 INT,
X2 INT,
The 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)
The SELECT ID_B, COUNT (*) AS AMOUNT_MATCH
The FROM
(SELECT ID_A ID_B, COUNT (*) AS AMOUNT
The 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
The JOIN CTE_1 C ON Anderson entries=C.I D
The JOIN CTE_2 D ON B.I D_B=D.I D AND c. ALUE_T1=D.V ALUE_T2
GROUP BY ID_A, ID_B
HAVING the COUNT (*) & gt; AS E=4)
GROUP BY ID_B