表1
jcxd 时间
项目1*项目2*项目3* 2011-1-2
项目3*项目4* 2011-1-5
项目1* 2011-1-4
表2
xm
项目1
项目2
项目3
项目4
项目5
我想查询2011-1-1到2011-1-5之间得出xm jcxd
项目1 2
项目2 1
项目3 2
项目4 1
项目5 0
jcxd 时间
项目1*项目2*项目3* 2011-1-2
项目3*项目4* 2011-1-5
项目1* 2011-1-4
表2
xm
项目1
项目2
项目3
项目4
项目5
我想查询2011-1-1到2011-1-5之间得出xm jcxd
项目1 2
项目2 1
项目3 2
项目4 1
项目5 0
解决方案 »
- SQL SERVER 一个关于数据统计的问题
- 关于分页存储过程排序问题
- 问一下两个表的查询问题
- 在sql server 2000的一个大容量表中,总是完不成删除记录的命令,总是提示超时,有什么办法可以做到?
- 写出查询的语句
- 请问: identity(1,1)与identity primary key 的区别是什么?
- 我是菜鸟 有条SQL语句请高手指点
- sql server 如何 禁止 删除 数据库?
- 在SQL Server 7.0里如何把数字格式化,如输入7(int)得到 0007(char)
- 写储存过程时怎样判断insert语句是否成功执行呢?
- 怎样调整字段的序号
- 看看这个查询sql语句怎么写
jcxd=(select count(1) from where tb1 charindex(a.xm+'*',jcxd+'*')>1
and 时间 between '2011-1-1' and '2011-1-5')
from tb2 a
create table t1
(
jcxd varchar(100),
date datetime
)
insert into t1
select '项目1*项目2*项目3*', '2011-1-2' union all
select '项目3*项目4*', '2011-1-5' union all
select '项目1*', '2011-1-4'
create table t2
(
xm varchar(10)
)
insert into t2
select '项目1' union all
select '项目2' union all
select '项目3' union all
select '项目4' union all
select '项目5'select t2.xm,(select count(*) as jcxd from t1 where charindex(t2.xm+'*',t1.jcxd)>=1
and t1.date>='2011-01-01' and t1.date<='2011-01-05') from t2
select *, (
len((select jcxd+'' from t1 for xml path('')))
- len(replace( (select jcxd+'' from t1 for xml path('')),tt.xm+'*',''))
)/len(tt.xm+'*') from t2 as tt
len((select jcxd+'' from t1 for xml path('')))
- len(replace( (select jcxd+'' from t1 where date>='2011-1-1' and date<='2011-1-5' for xml path('')),tt.xm+'*',''))
)/len(tt.xm+'*') from t2 as tt
select count(*) from t1 where charindex(tt.xm,jcxd)>=1 and date>='2011-1-1' and date<='2011-1-5'
)from t2 as tt
create table t1
(
jcxd varchar(100),
date datetime
)
insert into t1
select '项目1*项目2*项目3*', '2011-1-2' union all
select '项目3*项目4*', '2011-1-5' union all
select '项目1*', '2011-1-4' union all
select 'A项目1*', '2011-1-4'
create table t2
(
xm varchar(10)
)
insert into t2
select '项目1' union all
select '项目2' union all
select '项目3' union all
select '项目4' union all
select '项目5'select t2.xm,(select count(*) as jcxd from t1 where charindex('*'+t2.xm+'*','*'+t1.jcxd)>=1
and t1.date>='2011-01-01' and t1.date<='2011-01-05') from t2
drop table t1,t2/*
create table t1
(
jcxd varchar(100),
date datetime
)
insert into t1
select '项目1*项目2*项目3*', '2011-1-2' union all
select '项目3*项目4*', '2011-1-5' union all
select '项目1*', '2011-1-4' union all
select 'A项目1*', '2011-1-4'
create table t2
(
xm varchar(10)
)
insert into t2
select '项目1' union all
select '项目2' union all
select '项目3' union all
select '项目4' union all
select '项目5'select t2.xm,(select count(*) as jcxd from t1 where charindex('*'+t2.xm+'*','*'+t1.jcxd)>=1
and t1.date>='2011-01-01' and t1.date<='2011-01-05') from t2
drop table t1,t2
--建表
create table #xm (xm varchar(20))insert into #xm
select '项目1'
union all select '项目2'
union all select '项目3'
union all select '项目4'
union all select '项目5'--查询2011-1-1到2011-1-5之间得出select a.xm,count(date)
from #xm as a
left outer join #p as b on charindex('*'+a.xm+'*','*'+b.jcxd)>0 and b.date>='2011-1-1' and b.date<='2011-1-5'
group by a.xmxm
-------------------- -----------
项目1 2
项目2 1
项目3 2
项目4 1
项目5 0(5 行受影响)