有兩個資料表
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)
uj5u.com熱心網友回復：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/262725.html
標籤：基礎類
下一篇：數碼管顯示