34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE testabc (col1 int, col2 varchar(100))
INSERT testabc SELECT 53 ,'A'
UNION ALL SELECT 54 ,'A,B,C'
UNION ALL SELECT 55 ,'A,B,C,D'
UNION ALL SELECT 57 ,'B'
UNION ALL SELECT 78 ,'A,B,C,D'
go
CREATE TABLE testabb(col1 int, col2 varchar(100))
INSERT testabb SELECT 53 ,'B'
UNION ALL SELECT 54 ,'B,C,D'
UNION ALL SELECT 55 ,'B,C,D'
UNION ALL SELECT 57 ,'B'
UNION ALL SELECT 78 ,'A,B,C,D'
go
Create FUNCTION getTableB(@col int,@flag int)
RETURNS int
AS
BEGIN
DECLARE @a varchar(100),@b varchar(100)
DECLARE @i int,@j int,@k int,@js int,@r int
SELECT @j=0,@k=0,@js=0
SELECT @a=testabc.col2+',',@b=testabb.col2+',' FROM testabc INNER JOIN testabb ON testabc.col1=testabb.col1 WHERE testabc.col1=@col
WHILE charindex(',',@a)>0
BEGIN
SET @j=len(@b)-len(replace(@b,LEFT(@a,charindex(',',@a)-1),''))
SET @k=@k+@j
SET @js=@js+CASE WHEN @j=0 THEN 1 ELSE 0 END
SET @a=RIGHT(@a,len(@a)-charindex(',',@a))
END
SET @r=CASE WHEN @flag=0 THEN @k ELSE @js END
RETURN @r
END
go
SELECT *,
dbo.getTableB(col1,0) 与A表col2比对后相同字符数,
dbo.gettableB(col1,1) 与A表col2比对后不相同字符数
FROM testabb
--result
/*col1 col2 与A表col2比对后相同字符数 与A表col2比对后不相同字符数
----------- ------------------------------ --------------- ----------------
53 B 0 1
54 B,C,D 2 1
55 B,C,D 3 1
57 B 1 0
78 A,B,C,D 4 0
(所影响的行数为 5 行)*/
--> 测试数据: #A
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (col1 int,col2 varchar(7))
insert into #A
select 53,'A' union all
select 54,'A,B,C' union all
select 55,'A,B,C,D' union all
select 57,'B' union all
select 78,'A,B,C,D'
--> 测试数据: #B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B (col1 int,col2 varchar(7))
insert into #B
select 53,'B' union all
select 54,'B,C,D' union all
select 55,'B,C,D' union all
select 57,'B' union all
select 78,'A,B,C,D';
WITH PA (col1,P1,P2) AS
(
select col1,charindex(',',','+col2),charindex(',',col2+',')+1 from #A
union all
select a.col1,b.P2,charindex(',',col2+',',b.P2)+1 from #A a join PA b on a.col1=b.col1 where charindex(',',col2+',',b.P2)>0
),
PB (col1,P1,P2) AS
(
select col1,charindex(',',','+col2),charindex(',',col2+',')+1 from #B
union all
select a.col1,b.P2,charindex(',',col2+',',b.P2)+1 from #B a join PB b on a.col1=b.col1 where charindex(',',col2+',',b.P2)>0
),
A as
(
select a.col1,col2=substring(a.col2+',',b.P1,b.P2-b.P1-1) from #A a join PA b on a.col1=b.col1
),
B as
(
select a.col1,col2=substring(a.col2+',',b.P1,b.P2-b.P1-1) from #B a join PB b on a.col1=b.col1
)
select b.col1,count(a.col1) as 相同, count(1)-count(a.col1) as 不同 from B left join A on a.col1=b.col1 and a.col2=b.col2 group by b.col1
/*
col1 相同 不同
----------- ----------- -----------
53 0 1
54 2 1
55 3 0
57 1 0
78 4 0
*/
--col1 55应该为3,1
create table A(col1 int, col2 varchar(20))
insert into A values(53 , 'A')
insert into A values(54 , 'A,B,C')
insert into A values(55 , 'A,B,C,D')
insert into A values(57 , 'B')
insert into A values(78 , 'A,B,C,D')
create table B(col1 int, col2 varchar(20))
insert into B values(53 , 'B')
insert into B values(54 , 'B,C,D')
insert into B values(55 , 'B,C,D')
insert into B values(57 , 'B')
insert into B values(78 , 'A,B,C,D')
go
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
select tb1.col1 , isnull(tb2.cnt2,0) [与A表col2比对后相同字符数] , tb1.cnt1 - isnull(tb2.cnt2,0) [与A表col2比对后不相同字符数] from
(
select col1 , count(*) cnt1 from
(
SELECT m.col1, col2 = SUBSTRING(m.[col2], n.id, CHARINDEX(',', m.[col2] + ',', n.id) - n.id)
FROM A m, # n
WHERE SUBSTRING(',' + m.[col2], n.id, 1) = ','
) t
group by col1
) tb1 left join
(
select t1.col1 , count(*) cnt2 from
(
SELECT m.col1, col2 = SUBSTRING(m.[col2], n.id, CHARINDEX(',', m.[col2] + ',', n.id) - n.id)
FROM A m, # n
WHERE SUBSTRING(',' + m.[col2], n.id, 1) = ','
) t1 ,
(
SELECT m.col1, col2 = SUBSTRING(m.[col2], n.id, CHARINDEX(',', m.[col2] + ',', n.id) - n.id)
FROM B m, # n
WHERE SUBSTRING(',' + m.[col2], n.id, 1) = ','
) t2
where t1.col1 = t2.col1 and t1.col2 = t2.col2
group by t1.col1
) tb2
on tb1.col1 = tb2.col1
drop table A,B,#
/*
col1 与A表col2比对后相同字符数 与A表col2比对后不相同字符数
----------- --------------- ----------------
53 0 1
54 2 1
55 3 1
57 1 0
78 4 0
(所影响的行数为 5 行)
*/