我有表A 是一个标准表,B表的数据与这个表比对
表A 如下:
col1 col2
53 A
54 A,B,C
55 A,B,C,D
57 B
78 A,B,C,D
表B 如下: 需要得到第三列,第四列
col1 col2 与A表col2比对后相同字符数 与A表col2比对后不相同字符数
53 B 0 1
54 B,C,D 2 1
55 B,C,D 3 0
57 B 1 0
78 A,B,C,D 4 0
表A 如下:
col1 col2
53 A
54 A,B,C
55 A,B,C,D
57 B
78 A,B,C,D
表B 如下: 需要得到第三列,第四列
col1 col2 与A表col2比对后相同字符数 与A表col2比对后不相同字符数
53 B 0 1
54 B,C,D 2 1
55 B,C,D 3 0
57 B 1 0
78 A,B,C,D 4 0
把表B的col2的字段先加一个‘,’,
然后利用游标从表B中循环去COL1取每个逗号前的那个字符@char去表A中相同COL1的COL2中charindex(@char,A.COL2)<>0,用变量I来保存,找到个字符@char的个数,找到就I+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')
goSELECT 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.col1drop table A,B,#/*
col1 与A表col2比对后相同字符数 与A表col2比对后不相同字符数
----------- --------------- ----------------
53 0 1
54 2 1
55 3 1
57 1 0
78 4 0(所影响的行数为 5 行)
*/
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 行 第四列是=0 因为 col2中的每个字符都在表A的col2中
col1 col2
53 A
54 A,B,C
55 A,B,C,D
57 B
78 A,B,C,D
表B 如下: 需要得到第三列,第四列
col1 col2 与A表col2比对后相同字符数 与A表col2比对后不相同字符数
53 B 0 1
54 B,C,D 2 1
55 B,C,D 3 0
57 B 1 0
78 A,B,C,D 4 0
--------------------------------------------------------
表A
55 A,B,C,D
表B
55 B,C,DA不在?
如果算在其他的col1中,
那53,54,不是也为应该为0?
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 行)*/
表B 与 表A比对后
col1 col2 对 错
53 B 0 1
54 B,C,D 2 1
55 B,C,D 3 0
57 B 1 0
78 A,B,C,D 4 0