34,590
社区成员
发帖
与我相关
我的任务
分享
select Word,COUNT=(
select COUNT(1) from B where CHARINDEX(Word,Name)>0)
from A
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 行受影响)
;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
*/
declare @A table
(
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
*/
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)
*/