--- 構建資料表@t，實際有120W條記錄
DECLARE @t TABLE(Mid INT IDENTITY(1,1), m1 int,m2 int,m3 int,m4 int,m5 int)
INSERT @t select '1','2','3','4','5'
union all select '6','7','8','9','10'
union all select '11','12','13','14','15'
union all select '16','17','18','19','20'
--- 構建資料表@a，有3000條記錄
DECLARE @a TABLE(Nid INT IDENTITY(1,1), n1 int,n2 int,n3 int,n4 int,n5 int)
insert @a select '1','12','13','14','15'
union all select '2','12','13','14','15'
union all select '3','4','13','14','15'
union all select '4','6','8','14','15'
union all select '5','7','9','10','15'
union all select '16','17','18','19','20'
-----選擇@t的每一行 去和 @a里的每一行 每個欄位 逐一對比結果
SELECT mid1 mid,m1,m2,m3,m4,m5, [R0]=SUM(CASE WHEN counts=0 THEN 1 ELSE 0 END ),
[R1]=SUM(CASE WHEN counts=1 THEN 1 ELSE 0 END),
[R2]=SUM(CASE WHEN counts=2 THEN 1 ELSE 0 END),
[R3]=SUM(CASE WHEN counts=3 THEN 1 ELSE 0 END),
[R4]=SUM(CASE WHEN counts=4 THEN 1 ELSE 0 END),
[R5]=SUM(CASE WHEN counts=5 THEN 1 ELSE 0 END
)
FROM
(
SELECT b.*,a.mid mid1,m1,m2,m3,m4,m5,
Case when n1 in (Select m1 from @a ) then 1 else 0 end
+ (Case when n2 in (Select m2 from @a ) then 1 else 0 end)
+ (Case when n3 in (Select m3 from @a ) then 1 else 0 end)
+ (Case when n4 in (Select m4 from @a ) then 1 else 0 end)
+ (Case when n5 in (Select m5 from @a ) then 1 else 0 end)
counts
FROM @t a,@a b
)counts
GROUP BY mid1,m1,m2,m3,m4,m5
ORDER BY mid1
執行結果
表@t里 Mid=1的記錄 '1','2','3','4','5' 對比表@a里 每行Nid的5個欄位
有0個相同的記錄有1行 記作 R0=1
有1個相同的記錄有4行 記作 R1=4
有2個相同的記錄有1行 記作 R2=1
有3個相同的記錄有0行 記作 R3=0
有4個相同的記錄有0行 記作 R4=0
有5個相同的記錄有0行 記作 R5=0
想要的結果
uj5u.com熱心網友回復：
in反了吧這是uj5u.com熱心網友回復：
這是想要的結果
uj5u.com熱心網友回復：
不但in反了，而且查的也有問題，試試這個
--- 構建資料表@t，實際有120W條記錄
DECLARE @t TABLE(Mid INT IDENTITY(1,1), m1 int,m2 int,m3 int,m4 int,m5 int)
INSERT @t select '1','2','3','4','5'
union all select '6','7','8','9','10'
union all select '11','12','13','14','15'
union all select '16','17','18','19','20'
--- 構建資料表@a，有3000條記錄
DECLARE @a TABLE(Nid INT IDENTITY(1,1), n1 int,n2 int,n3 int,n4 int,n5 int)
insert @a select '1','12','13','14','15'
union all select '2','12','13','14','15'
union all select '3','4','13','14','15'
union all select '4','6','8','14','15'
union all select '5','7','9','10','15'
union all select '16','17','18','19','20'
-----選擇@t的每一行 去和 @a里的每一行 每個欄位 逐一對比結果
SELECT mid1 mid,m1,m2,m3,m4,m5, [R0]=SUM(CASE WHEN counts=0 THEN 1 ELSE 0 END ),
[R1]=SUM(CASE WHEN counts=1 THEN 1 ELSE 0 END),
[R2]=SUM(CASE WHEN counts=2 THEN 1 ELSE 0 END),
[R3]=SUM(CASE WHEN counts=3 THEN 1 ELSE 0 END),
[R4]=SUM(CASE WHEN counts=4 THEN 1 ELSE 0 END),
[R5]=SUM(CASE WHEN counts=5 THEN 1 ELSE 0 END
)
FROM
(
SELECT b.*,a.mid mid1,m1,m2,m3,m4,m5,
-- Case when n1 in (Select m1 from @a ) then 1 else 0 end
--+ (Case when n2 in (Select m2 from @a ) then 1 else 0 end)
--+ (Case when n3 in (Select m3 from @a ) then 1 else 0 end)
--+ (Case when n4 in (Select m4 from @a ) then 1 else 0 end)
--+ (Case when n5 in (Select m5 from @a ) then 1 else 0 end)
Case when m1 in (SELECT VAL FROM @a UNPIVOT ([VAL] FOR [N] IN ([N1],[N2],[N3],[N4],[N5])) T WHERE T.Nid = B.Nid) then 1 else 0 end
+ (Case when m2 in (SELECT VAL FROM @a UNPIVOT ([VAL] FOR [N] IN ([N1],[N2],[N3],[N4],[N5])) T WHERE T.Nid = B.Nid) then 1 else 0 end)
+ (Case when m3 in (SELECT VAL FROM @a UNPIVOT ([VAL] FOR [N] IN ([N1],[N2],[N3],[N4],[N5])) T WHERE T.Nid = B.Nid) then 1 else 0 end)
+ (Case when m4 in (SELECT VAL FROM @a UNPIVOT ([VAL] FOR [N] IN ([N1],[N2],[N3],[N4],[N5])) T WHERE T.Nid = B.Nid) then 1 else 0 end)
+ (Case when m5 in (SELECT VAL FROM @a UNPIVOT ([VAL] FOR [N] IN ([N1],[N2],[N3],[N4],[N5])) T WHERE T.Nid = B.Nid) then 1 else 0 end)
counts
FROM @t a,@a b
)counts
GROUP BY mid1,m1,m2,m3,m4,m5
ORDER BY mid1
uj5u.com熱心網友回復：
@t 表里4行資料 一一 去和 @a 表里的資料對比，想要的結果是
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/284041.html
標籤：疑難問題
上一篇：制作校園超市管理系統