表A有一个字段,Word,很多单词
表B有很多列,其中一列有一个字段Name,包含表A里的Word字符串。
想统计表A的Word在表B中出现的次数不知道怎么写,谢谢就是A表
Word
aa
bb
cc表B
Id Name
1 aa3343
2 cc234324
3 3432bb
4 2343bb能得到
aa 1
bb 2
cc 1
表B有很多列,其中一列有一个字段Name,包含表A里的Word字符串。
想统计表A的Word在表B中出现的次数不知道怎么写,谢谢就是A表
Word
aa
bb
cc表B
Id Name
1 aa3343
2 cc234324
3 3432bb
4 2343bb能得到
aa 1
bb 2
cc 1
from A
(
select 'aa'
union all select 'bb'
union all select 'cc'
),
B(Id,Name) as
(
select 1,'aa3343'
union all select 2,'cc234324'
union all select 3,'3432bb'
union all select 4,'2343bb'
)
select Word,COUNT=(select COUNT(1) from B where CHARINDEX(Word,Name)>0)
from A/*
Word COUNT
aa 1
bb 2
cc 1
*
create table 表A(Word varchar(10))insert into 表A
select 'aa' union all
select 'bb' union all
select 'cc'create table 表B
(Id int, name varchar(10))insert into 表B
select 1, 'aa3343' union all
select 2, 'cc234324' union all
select 3, '3432bb' union all
select 4, '2343bb'
select Word,qty
from 表A a
outer apply
(select count(1) 'qty' from 表B b
where charindex(a.Word,b.name,1)>0) t
/*
Word qty
---------- -----------
aa 1
bb 2
cc 1(3 row(s) affected)
*/
(
Word varchar(100)
)insert into @A
select 'aa' union all
select 'bb' union all
select 'cc'declare @B table
(
Id int,
Name varchar(100)
)
insert into @B
select 1 , 'aa3343' union all
select 2 , 'cc234324' union all
select 3 , '3432bb' union all
select 4 , '2343bb'select Word,
COUNT(b.Name)
from @A a
left join @B b
on b.Name like '%'+a.word+'%'
group by Word
/*
Word (无列名)
aa 1
bb 2
cc 1
*/
--或者:;with A(word) as
(
select 'aa'
union all select 'bb'
union all select 'cc'
),
B(Id,Name) as
(
select 1,'aa3343'
union all select 2,'cc234324'
union all select 3,'3432bb'
union all select 4,'2343bb'
)select Word,SUM(case when CHARINDEX(Word,Name)>0 then 1 else 0 end) as count
from A
left join B on CHARINDEX(Word,Name)>0
group by Word/*
Word count
aa 1
bb 2
cc 1
*/
create table #a (word varchar(50))
insert into #a
select 'aa' union all
select 'bb' union all
select 'cc'create table #b(id int,name varchar(50))
insert into #b
select 1,'aa3343' union all
select 2,'cc234324' union all
select 3,'3432bb' union all
select 4,'2343bb'select word,count(*)qty from (
select * from #a,#b
) cte where word=substring(name,patindex('%[a-zA-Z]%',name),2)
group by word
--------------------------------------------------------------
word qty
-------------------------------------------------- -----------
aa 1
bb 2
cc 1(3 行受影响)
其实上面的方法都是模糊查询相当于需要对2万*7万,也就是14亿的数据量,确实会超时的。最好表B的name字段中的字符串,能有分隔符,比如'aa,3344',这样的话,可以通过函数先把b表的2万条数据,先拆分,然后就可以直接和表a关联,这样速度应该会快的
select Word,COUNT=(
select COUNT(1) from B where CHARINDEX(Word,Name)>0)
from A
那你就把A表拆成N个小表,每个不超过M行记录,M取决于你那机器牛B的程度,你可以用把原语句里的FROM A改成FROM (SELECT TOP M FROM A)的形式来测试多少是可行的,M从1开始,以1,10,50,100,500,1000这几个数字递增。