表1
jcxd 时间
项目1*项目2*项目3 2011-1-2
项目3*项目4 2011-1-5
项目1* 2011-1-4
表2
xm
项目1
项目2
项目3
项目4
我想查询2011-1-1到2011-1-5之间得出xm jcxd
项目1 2
项目2 1
项目3 2
项目4 1
jcxd 时间
项目1*项目2*项目3 2011-1-2
项目3*项目4 2011-1-5
项目1* 2011-1-4
表2
xm
项目1
项目2
项目3
项目4
我想查询2011-1-1到2011-1-5之间得出xm jcxd
项目1 2
项目2 1
项目3 2
项目4 1
select xm,
jcxd=(select count(1) from tb2 where charindex(a.xm+'*',jcxd+'*')>0
and 时间 between '2011-1-1' and '2011-1-5')
from tb1 a
--表搞反了
select xm,
jcxd=(select count(1) from tb1 where charindex(a.xm+'*',jcxd+'*')>0
and 时间 between '2011-1-1' and '2011-1-5')
from tb2 a
declare @表1 table (jcxd varchar(17),时间 datetime)
insert into @表1
select '项目1*项目2*项目3','2011-1-2' union all
select '项目3*项目4','2011-1-5' union all
select '项目1*','2011-1-4'declare @表2 table (xm varchar(5))
insert into @表2
select '项目1' union all
select '项目2' union all
select '项目3' union all
select '项目4'select xm,[count]=sum(case when [sign]=0 then 0 else 1 end )
from (select a.xm,charindex(a.xm,b.jcxd) as [sign] from @表2 a left join
@表1 b on 1=1 where b.时间 between '2011-1-1' and '2011-1-5')
a group by xm
/*
xm count
----- -----------
项目1 2
项目2 1
项目3 2
项目4 1
*/
jcxd=(select count(1) from tb1 where charindex(a.xm+'*',jcxd+'*')>0
and 时间 between '2011-1-1' and '2011-1-5')
from tb2 a
insert into tb1
select '项目1' union all
select '项目2' union all
select '项目3' union all
select '项目4'create table tb2 (jcxd varchar(17),时间 datetime)
insert into tb2
select '项目1*项目2*项目3','2011-1-2' union all
select '项目3*项目4','2011-1-5' union all
select '项目1*','2011-1-4'select xm,ic=(select count(jcxd) ic from tb2 where 时间 between '2011-1-1' and '2011-1-5' and charindex(tb1.xm,jcxd)>0)
from tb1[/code]
还是那句,重在参与,顺便学习高人
jcxd=(select count(1) from tb1 where charindex(a.xm+'*',jcxd+'*')>0
and 时间 between '2011-1-1' and '2011-1-5')
from tb2 a