zjcxc(邹建) 继续请教

[caofuyuan]

表A的格式如下：
ID ITEM
100 [A，C，D]
200 [B，C，E]
300 [A，B，C，E]
400 [B，E]
表B的格式如下：
AA BB
1 [A，B]
2 [A，C]
3 [A，E]
4 [B，C]
5 [B，E]
6 [C，E]
要产生的新表C为
AA BB NUM
1 [A，B]
2 [A，C]
3 [A，E]
4 [B，C]
5 [B，E]
6 [C，E]

也就是要求出B表的BB列的每一个元素在A表中ITEM列中出现的次数。[A，B]的次数为1，[A，C]=2；[A，E]=1
[B，C]=2；[B，E]=3，[C，E]=2；
谢谢

[zjcxc]

--统计示例

--测试数据
create table ta(ID int,ITEM varchar(20))
insert ta select 100, '[A，C，D]'
union all select 200, '[B，C，E]'
union all select 300, '[A，B，C，E]'
union all select 400, '[C，E]'

create table tb(ID int,ITEM varchar(20))
insert tb select 1,'[A，B]'
union all select 2,'[A，C]'
union all select 3,'[A，E]'
union all select 4,'[B，C]'
union all select 5,'[B，E]'
union all select 6,'[C，E]'
go

--统计
declare @i int
select @i=max(len(ITEM))-2 from ta
set rowcount @i
select id=identity(int) into #t from syscolumns a,syscolumns b
set rowcount 0

select ID,ITEM,NUM=count(*)
from(
select b.ID,b.ITEM
from(
select a.ID,ITEM=','+substring(a.ITEM,b.id,charindex(',',a.ITEM+',',b.id)-b.id)+','
from(select ID,ITEM=substring(ITEM,2,len(ITEM)-2) from ta)a,#t b
where b.id<=len(a.ITEM) and substring(','+a.ITEM,b.id,1)=','
)a join tb b on charindex(a.ITEM,','+substring(b.ITEM,2,len(b.ITEM)-2)+',')>0
group by b.ID,b.ITEM,a.ID
having count(*)=len(b.ITEM)-len(replace(b.ITEM,',',''))+1
)a group by ID,ITEM

drop table #t
go

--删除测试
drop table ta,tb

/*--测试结果

ID ITEM NUM
----------- -------------------- -----------
1 [A，B] 1
2 [A，C] 2
3 [A，E] 1
4 [B，C] 2
5 [B，E] 2
6 [C，E] 3

（所影响的行数为 6 行）
*/

[hisi]

哈哈，这种问题怎么看怎么像是在考试。。

[zjcxc]

BB固定由两个字母组成?

[hisi]

/*创建函数*/
create function fSplit( @msg as varchar(1300), @spe as char(1) )
returns @t table
(
[id] int primary key,
msg varchar(1300)
)
begin

if @msg is null or @spe is null return ;

declare @i int ;
declare @l int ;
declare @j int ;

set @msg = ltrim(rtrim(@msg)) ;

set @l = len( @msg ) ;
set @j = 1 ;
while ( 1 = 1 )
begin

set @i = charindex( @spe, @msg ) ;
if @i > 0
begin
insert into @t ( id, msg ) select @j, left( @msg, @i-1 ) ;
set @l = @l - @i ;
set @j = @j + 1 ;
set @msg = substring( @msg, @i + 1, @l ) ;
end
else
begin
insert into @t select @j, @msg ;
break ;
end

end

return ;
end
go

create function fCheckIn( @item1 varchar(8000), @item2 varchar(8000) )
returns int
begin

set @item1 = substring(@item1, 2, len(@item1)-2 )
set @item2 = substring(@item2, 2, len(@item2)-2 )

declare @count1 int
select @count1 = count(*) from dbo.fSplit(@item1,',')
if ( select count(*) from dbo.fSplit(@item1,',') A, dbo.fSplit(@item2,',') B where A.msg=B.msg ) = @count1
return 1
return 0
end
go

--测试数据
declare @A table( ID int, ITEM varchar(20) )
insert into @A
select 100, '[A，C，D]'
union all select 200, '[B，C，E]'
union all select 300, '[A，B，C，E]'
union all select 400, '[B，E]'

declare @B table( ID int, ITEM varchar(20) )
insert into @B
select 1,'[A，B]'
union all select 2,'[A，C]'
union all select 3,'[A，E]'
union all select 4,'[B，C]'
union all select 5,'[B，E]'
union all select 6,'[C，E]'

--查询
select *, (select count(*) from @A where dbo.fCheckIn(B.item,item)=1) as NUM from @B B

--删除函数
drop function fCheckIn
drop function fSplit

/*
结果：
ID ITEM NUM
----------- -------------------- -----------
1 [A，B] 1
2 [A，C] 2
3 [A，E] 1
4 [B，C] 2
5 [B，E] 3
6 [C，E] 2
*/