This code execution result is wrong, how is the solution-CodePudding

 
- build a data table @ t, actually has a 120 w record 
DECLARE @ t TABLE (mids INT IDENTITY (1, 1), INT, m1 m2 INT, m3 INT, m4 INT, INT m5) 
INSERT the @ t select '1', '2', '3', '4', '5' 
Union all select '6', '7', '8', '9', '10' 
Union all select '11', '12' and '13', '14' and '15' 
Union all select '16', '17', '18', 'the', '20' 

- build a data table @ a, there are 3000 records 
DECLARE @ a TABLE (Nid INT IDENTITY (1, 1), n1 INT, n2 INT, n3 INT, n4 interchange INT, n5 INT) 
Insert @ a select '1', '12', '13', '14' and '15' 
Union all select '2', '12' and '13', '14' and '15' 
Union all select '3', '4', '13', '14' and '15' 
Union all select '4', '6', '8', '14' and '15' 
Union all select '5', '7', '9', '10', '15' 
Union all select '16', '17', '18', 'the', '20' 

-- -- -- -- -- choose @ t to each row and @ each line in a field by comparing the results 
SELECT mid1 mids, 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 one 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 END THEN 1 ELSE 0 
) 
The FROM 
(
B. SELECT *, arjun id mid1, m1, m2, m3, m4, m5, 
Case the when n1 in (Select from m1 @ a) then one else 0 end 
+ (Case when n2 in the from (Select m2 @ a) then one else 0 end) 
+ (Case when n3 (Select m3 in the from @ a) then one else 0 end) 
+ (Case when n4 interchange in (Select the m4 from @ a) then one else 0 end) 
+ (Case when n5 in (Select the m5 from @ a) then one else 0 end) 
Counts 
The FROM @ t a, @ a b 
) counts 
GROUP BY mid1, m1, m2, m3, m4, m5 
The ORDER BY mid1

The results

The

reference

mid m1, m2, m3 m4, m5 R0 R1 R2 and R3 and R4 R5
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1 2 3 4 5 5 1 0 0 0 0

In Mid table @ t=1 records' 1 ', '2', '3', '4', '5' contrast table @ the five fields of Nid in each row in a

Has the same record of zero line 1 as R0=1
One the same record four lines as R1=4
Have two the same record 1 row as R2=1
There are three had the same record row 0 as R3=0
There are four the same record have 0 line as R4=0
There are five (5) the same record have 0 line as R5=0
Want
The

reference

mid m1, m2, m3 m4, m5 R0 R1 R2 and R3 and R4 R5
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1 1 2 3 4 5 4 1 0 0 0

CodePudding user response:

In the this is

CodePudding user response:

The

reference

mid m1, m2, m3 m4, m5 R0 R1 R2 and R3 and R4 R5
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1 2 3 4 5 1 4 1 0 0 0
2, 6, 7 8 9 10 4 0 1 1 0 0
3 11 12 13 14 15 1 1 1 1 2 0
4 16 17 18 19 20 5 0 0 0 0 1

This is the desired result

CodePudding user response:

Not only in reverse, and also has a problem, try this

 
- build a data table @ t, actually has a 120 w record 
DECLARE @ t TABLE (mids INT IDENTITY (1, 1), INT, m1 m2 INT, m3 INT, m4 INT, INT m5) 
INSERT the @ t select '1', '2', '3', '4', '5' 
Union all select '6', '7', '8', '9', '10' 
Union all select '11', '12' and '13', '14' and '15' 
Union all select '16', '17', '18', 'the', '20' 

- build a data table @ a, there are 3000 records 
DECLARE @ a TABLE (Nid INT IDENTITY (1, 1), n1 INT, n2 INT, n3 INT, n4 interchange INT, n5 INT) 
Insert @ a select '1', '12', '13', '14' and '15' 
Union all select '2', '12' and '13', '14' and '15' 
Union all select '3', '4', '13', '14' and '15' 
Union all select '4', '6', '8', '14' and '15' 
Union all select '5', '7', '9', '10', '15' 
Union all select '16', '17', '18', 'the', '20' 

-- -- -- -- -- choose @ t to each row and @ each line in a field by comparing the results 
SELECT mid1 mids, 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 one 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 END THEN 1 ELSE 0 
) 
The FROM 
(
B. SELECT *, arjun id mid1, m1, m2, m3, m4, m5, 
- Case when n1 in (Select from m1 @ a) then one else 0 end 
- + (Case when n2 in the from (Select m2 @ a) then one else 0 end) 
- + (Case when n3 (Select m3 in the from @ a) then one else 0 end) 
- + (Case when n4 interchange in (Select the m4 from @ a) then one else 0 end) 
- + (Case when n5 in (Select the m5 from @ a) then one else 0 end) 
Case when the m1 (SELECT in VAL FROM @ a UNPIVOT ([VAL] FOR [N] in [[N1], [N], [N3], [N4 interchange], [N5])) T WHERE T.N id=B.N id) then one else 0 end 
+ (Case when m2 (SELECT in VAL FROM @ a UNPIVOT ([VAL] FOR [N] in [[N1], [N], [N3], [N4 interchange], [N5])) T WHERE T.N id=B.N id) then one else 0 end) 
+ (Case when m3 (SELECT in VAL FROM @ a UNPIVOT ([VAL] FOR [N] in [[N1], [N], [N3], [N4 interchange], [N5])) T WHERE T.N id=B.N id) then one else 0 end) 
nullnullnullnullnullnullnullnullnullnullnullnullnullnull