如果一次只取某一个值的个数倒好办:
select count(*)
from tab1
where charindex('01',w)<>0若想一次把所有W中包含的值都列出来,就有点不好办了。我想应该另外有表会得到01\80\17\13这些值的清单吧,那就用:
select tab2.name,
(select count(*) from tab1 where charindex(tab2.name,w)<>0) as fcount
from tab2
select count(*)
from tab1
where charindex('01',w)<>0若想一次把所有W中包含的值都列出来,就有点不好办了。我想应该另外有表会得到01\80\17\13这些值的清单吧,那就用:
select tab2.name,
(select count(*) from tab1 where charindex(tab2.name,w)<>0) as fcount
from tab2
SUM(CASE WHEN charindex('80',w)<>0 THEN 1 END 0 END) AS [80],
SUM(CASE WHEN charindex('17',w)<>0 THEN 1 END 0 END) AS [17],
...
FROM TAB1
SUM(CASE WHEN charindex('80',w)>0 THEN 1 END 0 END) AS [80],
SUM(CASE WHEN charindex('17',w)>0 THEN 1 END 0 END) AS [17],
SUM(CASE WHEN charindex('13',w)>0 THEN 1 END 0 END) AS [13],
FROM TAB1
[80]=SUM(CASE WHEN charindex('80',w)>0 THEN 1 END 0 END),
[17]=SUM(CASE WHEN charindex('17',w)>0 THEN 1 END 0 END),
[13]=SUM(CASE WHEN charindex('13',w)>0 THEN 1 END 0 END)
FROM TAB1
,计数=count(distinct a.日)
from Tab1 a,(
select id=1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9
union all select 10 union all select 11 union all select 12
)b where b.id<=len(a.W) and charindex(' ',' '+a.W,b.id)=b.id
group by substring(a.W,b.id,charindex(' ',a.W+' ',b.id)-b.id)
create table Tab1(日 int ,W char(12))
insert Tab1 select 1,'01 80 80'
union all select 2,'01 17 13'
union all select 3,'01'
union all select 4,'01 80 17'
union all select 5,'13'
union all select 6,'01'
union all select 7,'01 80 01'
union all select 8,''
union all select 9,'01 01 80 17'
union all select 10,''
union all select 11,'01 80'
union all select 12,'80 01 17'
union all select 13,'80'
union all select 14,'01 80'
union all select 15,'80 17'
go--统计处理
select W=substring(a.W,b.id,charindex(' ',a.W+' ',b.id)-b.id)
,计数=count(distinct a.日)
from Tab1 a,(
select id=1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9
union all select 10 union all select 11 union all select 12
)b where b.id<=len(a.W) and charindex(' ',' '+a.W,b.id)=b.id
group by substring(a.W,b.id,charindex(' ',a.W+' ',b.id)-b.id)
go--删除测试
drop table Tab1/*--测试结果W 计数
------------ -----------
01 10
13 2
17 5
80 9(所影响的行数为 4 行)
--*/