逐一数据对比修改为选定数据对比
执行以下代码
DECLARE @t TABLE(id INT IDENTITY(1,1), no varchar(20))
INSERT @t select '10,11,12,13,26,28'--- ID01
union all select '04,09,19,20,21,26'--- ID02
union all select '01,07,10,23,28,32'--- ID03
union all select '04,06,07,10,13,25'--- ID04
union all select '04,06,15,17,30,31'--- ID05
union all select '01,03,10,21,26,27'--- ID06
union all select '01,09,19,21,23,26'--- ID07
union all select '05,08,09,14,17,23'--- ID08
union all select '05,09,18,20,22,30'--- ID09
union all select '01,02,08,13,17,24'--- ID10
union all select '04,05,11,12,30,32'--- ID11
union all select '02,12,16,17,27,30'--- ID12
union all select '08,13,17,21,23,32'--- ID13
union all select '03,05,07,08,21,31'--- ID14
union all select '04,11,19,25,26,32'--- ID15
union all select '11,17,28,30,31,33'--- ID16
DECLARE @a TABLE(id INT IDENTITY(1,1), no1 CHAR(2),no2 CHAR(2),no3 CHAR(2),no4 CHAR(2),no5 CHAR(2),no6 CHAR(2))
INSERT @a select '10','11','12','13','26','28'--- ID01
union all select '04','09','19','20','21','26'--- ID02
union all select '01','07','10','23','28','32'--- ID03
union all select '04','06','07','10','13','25'--- ID04
union all select '04','06','15','17','30','31'--- ID05
union all select '01','03','10','21','26','27'--- ID06
union all select '01','09','19','21','23','26'--- ID07
union all select '05','08','09','14','17','23'--- ID08
union all select '05','09','18','20','22','30'--- ID09
union all select '01','02','08','13','17','24'--- ID10
union all select '04','05','11','12','30','32'--- ID11
union all select '02','12','16','17','27','30'--- ID12
union all select '08','13','17','21','23','32'--- ID13
union all select '03','05','07','08','21','31'--- ID14
union all select '04','11','19','25','26','32'--- ID15
union all select '11','17','28','30','31','33'--- ID16
SELECT id1 id,no, [R1]=SUM(CASE WHEN aaa=0 THEN 1 ELSE 0 END ),
[R2]=SUM(CASE WHEN aaa=1 THEN 1 ELSE 0 END),
[R3]=SUM(CASE WHEN aaa=2 THEN 1 ELSE 0 END),
[R4]=SUM(CASE WHEN aaa=3 THEN 1 ELSE 0 END),
[R5]=SUM(CASE WHEN aaa=4 THEN 1 ELSE 0 END),
[R6]=SUM(CASE WHEN aaa=5 THEN 1 ELSE 0 END),
[R7]=SUM(CASE WHEN aaa=6 THEN 1 ELSE 0 END)
FROM
(
SELECT b.*,a.id id1,no,
CASE WHEN CHARINDEX(','+no1+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no2+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no3+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no4+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no5+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no6+',',','+no+',')>0 THEN 1 ELSE 0 END
aaa
FROM @t a,@a b
)aaa
GROUP BY id1,no
ORDER BY id1
