27,579
社区成员
发帖
与我相关
我的任务
分享
;with t(ID,c1,c2) AS (
select 1,'1100011111000111','1011111010000010' union ALL
select 2,'1100011110100111','1111011010111011'
)
SELECT t.ID,count(0) as cnt from t
INNER join master.dbo.spt_values as sv on sv.type='P' and sv.number between 1 and len(c1)
where substring(t.c1,sv.number,1)=substring(t.c2,sv.number,1)
GROUP BY t.ID
1 8
2 10
USE tempdb
GO
--创建表
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(c1 VARCHAR(50),c2 VARCHAR(50))
--添加计算列(你做这一步就可以了)
ALTER TABLE t ADD equalsChars AS
CASE WHEN LEN(c1)>=1 AND LEN(c2)>=1 AND LEFT(RIGHT(c1,1),1) = LEFT(RIGHT(c2,1),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=2 AND LEN(c2)>=2 AND LEFT(RIGHT(c1,2),1) = LEFT(RIGHT(c2,2),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=3 AND LEN(c2)>=3 AND LEFT(RIGHT(c1,3),1) = LEFT(RIGHT(c2,3),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=4 AND LEN(c2)>=4 AND LEFT(RIGHT(c1,4),1) = LEFT(RIGHT(c2,4),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=5 AND LEN(c2)>=5 AND LEFT(RIGHT(c1,5),1) = LEFT(RIGHT(c2,5),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=6 AND LEN(c2)>=6 AND LEFT(RIGHT(c1,6),1) = LEFT(RIGHT(c2,6),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=7 AND LEN(c2)>=7 AND LEFT(RIGHT(c1,7),1) = LEFT(RIGHT(c2,7),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=8 AND LEN(c2)>=8 AND LEFT(RIGHT(c1,8),1) = LEFT(RIGHT(c2,8),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=9 AND LEN(c2)>=9 AND LEFT(RIGHT(c1,9),1) = LEFT(RIGHT(c2,9),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=10 AND LEN(c2)>=10 AND LEFT(RIGHT(c1,10),1) = LEFT(RIGHT(c2,10),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=11 AND LEN(c2)>=11 AND LEFT(RIGHT(c1,11),1) = LEFT(RIGHT(c2,11),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=12 AND LEN(c2)>=12 AND LEFT(RIGHT(c1,12),1) = LEFT(RIGHT(c2,12),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=13 AND LEN(c2)>=13 AND LEFT(RIGHT(c1,13),1) = LEFT(RIGHT(c2,13),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=14 AND LEN(c2)>=14 AND LEFT(RIGHT(c1,14),1) = LEFT(RIGHT(c2,14),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=15 AND LEN(c2)>=15 AND LEFT(RIGHT(c1,15),1) = LEFT(RIGHT(c2,15),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=16 AND LEN(c2)>=16 AND LEFT(RIGHT(c1,16),1) = LEFT(RIGHT(c2,16),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=17 AND LEN(c2)>=17 AND LEFT(RIGHT(c1,17),1) = LEFT(RIGHT(c2,17),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=18 AND LEN(c2)>=18 AND LEFT(RIGHT(c1,18),1) = LEFT(RIGHT(c2,18),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=19 AND LEN(c2)>=19 AND LEFT(RIGHT(c1,19),1) = LEFT(RIGHT(c2,19),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=20 AND LEN(c2)>=20 AND LEFT(RIGHT(c1,20),1) = LEFT(RIGHT(c2,20),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=21 AND LEN(c2)>=21 AND LEFT(RIGHT(c1,21),1) = LEFT(RIGHT(c2,21),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=22 AND LEN(c2)>=22 AND LEFT(RIGHT(c1,22),1) = LEFT(RIGHT(c2,22),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=23 AND LEN(c2)>=23 AND LEFT(RIGHT(c1,23),1) = LEFT(RIGHT(c2,23),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=24 AND LEN(c2)>=24 AND LEFT(RIGHT(c1,24),1) = LEFT(RIGHT(c2,24),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=25 AND LEN(c2)>=25 AND LEFT(RIGHT(c1,25),1) = LEFT(RIGHT(c2,25),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=26 AND LEN(c2)>=26 AND LEFT(RIGHT(c1,26),1) = LEFT(RIGHT(c2,26),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=27 AND LEN(c2)>=27 AND LEFT(RIGHT(c1,27),1) = LEFT(RIGHT(c2,27),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=28 AND LEN(c2)>=28 AND LEFT(RIGHT(c1,28),1) = LEFT(RIGHT(c2,28),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=29 AND LEN(c2)>=29 AND LEFT(RIGHT(c1,29),1) = LEFT(RIGHT(c2,29),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=30 AND LEN(c2)>=30 AND LEFT(RIGHT(c1,30),1) = LEFT(RIGHT(c2,30),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=31 AND LEN(c2)>=31 AND LEFT(RIGHT(c1,31),1) = LEFT(RIGHT(c2,31),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=32 AND LEN(c2)>=32 AND LEFT(RIGHT(c1,32),1) = LEFT(RIGHT(c2,32),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=33 AND LEN(c2)>=33 AND LEFT(RIGHT(c1,33),1) = LEFT(RIGHT(c2,33),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=34 AND LEN(c2)>=34 AND LEFT(RIGHT(c1,34),1) = LEFT(RIGHT(c2,34),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=35 AND LEN(c2)>=35 AND LEFT(RIGHT(c1,35),1) = LEFT(RIGHT(c2,35),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=36 AND LEN(c2)>=36 AND LEFT(RIGHT(c1,36),1) = LEFT(RIGHT(c2,36),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=37 AND LEN(c2)>=37 AND LEFT(RIGHT(c1,37),1) = LEFT(RIGHT(c2,37),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=38 AND LEN(c2)>=38 AND LEFT(RIGHT(c1,38),1) = LEFT(RIGHT(c2,38),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=39 AND LEN(c2)>=39 AND LEFT(RIGHT(c1,39),1) = LEFT(RIGHT(c2,39),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=40 AND LEN(c2)>=40 AND LEFT(RIGHT(c1,40),1) = LEFT(RIGHT(c2,40),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=41 AND LEN(c2)>=41 AND LEFT(RIGHT(c1,41),1) = LEFT(RIGHT(c2,41),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=42 AND LEN(c2)>=42 AND LEFT(RIGHT(c1,42),1) = LEFT(RIGHT(c2,42),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=43 AND LEN(c2)>=43 AND LEFT(RIGHT(c1,43),1) = LEFT(RIGHT(c2,43),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=44 AND LEN(c2)>=44 AND LEFT(RIGHT(c1,44),1) = LEFT(RIGHT(c2,44),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=45 AND LEN(c2)>=45 AND LEFT(RIGHT(c1,45),1) = LEFT(RIGHT(c2,45),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=46 AND LEN(c2)>=46 AND LEFT(RIGHT(c1,46),1) = LEFT(RIGHT(c2,46),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=47 AND LEN(c2)>=47 AND LEFT(RIGHT(c1,47),1) = LEFT(RIGHT(c2,47),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=48 AND LEN(c2)>=48 AND LEFT(RIGHT(c1,48),1) = LEFT(RIGHT(c2,48),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=49 AND LEN(c2)>=49 AND LEFT(RIGHT(c1,49),1) = LEFT(RIGHT(c2,49),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(c1)>=50 AND LEN(c2)>=50 AND LEFT(RIGHT(c1,50),1) = LEFT(RIGHT(c2,50),1) THEN 1 ELSE 0 END Persisted
INSERT INTO t(c1,c2) VALUES('1100011111000111','1011111010000010')
/*
'1100011111000111'
'1011111010000010'
*/
SELECT * FROM t
/*
c1 c2 equalsChars
1100011111000111 1011111010000010 8
*/