try:
先建一个自定义函数:
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
endselect * from 表 a
where exists(select 1 from dbo.f_splitstr(a.times,'*') where datediff(day,F1,getdate())<=10)
先建一个自定义函数:
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
endselect * from 表 a
where exists(select 1 from dbo.f_splitstr(a.times,'*') where datediff(day,F1,getdate())<=10)
我假设数据表结构和演示数据如下:
ID names test times
001 A aaa*bbb*ccc 2003-1-20*2003-5-20*2003-11-26
002 B eee*fff 2003-10-1*2003-11-20
003 C aaa*ddd 2003-5-1*2003-10-3
004 D ccc 2002-10-15
005 E hhh*ddd*iii*ggg 2003-5-12*2003-10-5*2003-10-28*2003-11-28
我还是按上面的方式查询,我怎么样把
"今天是2003-11-28,我要查近十天的记录话"id names test times
001 A ccc 2003-11-26
002 B fff 2003-11-20
005 E ggg 2003-11-28
一一对应起来呢??
http://expert.csdn.net/Expert/topic/2483/2483747.xml?temp=.100445
create function f_chk(
@dt datetime, --要查询的时间
@times varchar(1000)
)
returns bit
as
begin
declare @re bit
declare @tb table(id int identity(1,1),i int)
insert into @tb(i) select top 8000 1 from
(select top 100 id from syscolumns) a,
(select top 100 id from syscolumns) bif exists(
select 1 from(
select dt=substring(name,b.id,charindex('*',name+'*',b.id)-b.id)
from (select name=@times) a,@tb b
where substring('*'+name,b.id,1)='*'
) a where case when isdate(dt)=1
then case when datediff(day,dt,@dt) between -10 and 10 then 1 else 0 end
else 0 end=1)
set @re=1
else
set @re=0
return(@re)
end
go--调用自定义得到你的结果
select * from tb where dbo.f_chk(getdate(),times)=1
create table tb(ID varchar(3),names varchar(1),times varchar(1000))
insert into tb
select '001','A','2003-1-20*2003-5-20*2003-11-26'
union all select '002','B','2003-10-1*2003-11-20'
union all select '003','C','2003-5-1*2003-10-3'
union all select '004','D','2002-10-15'
union all select '005','E','2003-5-12*2003-10-5*2003-10-28*2003-11-28'go
--创建自定义函数
create function f_chk(
@dt datetime, --要查询的时间
@times varchar(1000)
)
returns bit
as
begin
declare @re bit
declare @tb table(id int identity(1,1),i int)
insert into @tb(i) select top 8000 1 from
(select top 100 id from syscolumns) a,
(select top 100 id from syscolumns) bif exists(
select 1 from(
select dt=substring(name,b.id,charindex('*',name+'*',b.id)-b.id)
from (select name=@times) a,@tb b
where substring('*'+name,b.id,1)='*'
) a where case when isdate(dt)=1
then case when datediff(day,dt,@dt) between -10 and 10 then 1 else 0 end
else 0 end=1)
set @re=1
else
set @re=0
return(@re)
end
go--调用自定义得到你的结果
select * from tb where dbo.f_chk(getdate(),times)=1go
--删除测试环境
drop table tb
drop function f_chk/*--测试结果
ID names times
---- ----- ------------------------------------------
001 A 2003-1-20*2003-5-20*2003-11-26
002 B 2003-10-1*2003-11-20
005 E 2003-5-12*2003-10-5*2003-10-28*2003-11-28(所影响的行数为 3 行)--*/
insert t1 select '001','A','2003-1-20*2003-5-20*2003-11-26'
union all select '002','B','2003-10-1*2003-11-20'
union all select '003','C','2003-5-1*2003-10-3'
union all select '004','D','2002-10-15'
union all select '005','E','2003-5-12*2003-10-5*2003-10-28*2003-11-28'
select convert(char(10),dateadd(day,-id,getdate()),120) as rq into #tmp from
(
Select top 10 (select sum(1) from sysobjects where name<= a.name)-1 as id from sysobjects a
) aaselect b.* from #tmp a
join t1 b on charindex('*'+a.rq+'*','*'+b.times+'*')>0id names times
---- ----- ---------------------------------
001 A 2003-1-20*2003-5-20*2003-11-26
002 B 2003-10-1*2003-11-20
005 E 2003-5-12*2003-10-5*2003-10-28*2003-11-28(所影响的行数为 3 行)
create table tb(ID varchar(3),names varchar(1),times varchar(1000))
insert into tb
select '001','A','2003-1-20*2003-5-20*2003-11-26'
union all select '002','B','2003-10-1*2003-11-20'
union all select '003','C','2003-5-1*2003-10-3'
union all select '004','D','2002-10-15'
union all select '005','E','2003-5-12*2003-10-5*2003-10-28*2003-11-28'--得到第二种效果
declare @dt datetime
set @dt=getdate() --要查询的时间
select top 8000 id=identity(int,1,1) into #tb from
(select top 100 id from syscolumns) a,
(select top 100 id from syscolumns) bselect * from(
select a.id,a.names
,times=substring(a.times,b.id,charindex('*',a.times+'*',b.id)-b.id)
from tb a,#tb b
where substring('*'+a.times,b.id,1)='*'
) a where datediff(day,times,@dt) between -10 and 10go
--删除测试环境
drop table #tb
drop table tb/*--测试结果
id names times
---- ----- -------------
002 B 2003-11-20
001 A 2003-11-26
005 E 2003-11-28(所影响的行数为 3 行)
--*/
create table tb(ID varchar(3),names varchar(1),times varchar(1000))
insert into tb
select '001','A','2003-1-20*2003-5-20*2003-11-26'
union all select '002','B','2003-10-1*2003-11-20'
union all select '003','C','2003-5-1*2003-10-3'
union all select '004','D','2002-10-15'
union all select '005','E','2003-5-12*2003-10-5*2003-10-28*2003-11-28'go
--得到第二种效果,(再改一下,用存储过程来实现,方便调用)
create proc p_qry
@dt datetime --要查询的时间
as
set nocount on
select top 8000 id=identity(int,1,1) into #tb from
(select top 100 id from syscolumns) a,
(select top 100 id from syscolumns) bselect * from(
select a.id,a.names
,times=substring(a.times,b.id,charindex('*',a.times+'*',b.id)-b.id)
from tb a,#tb b
where substring('*'+a.times,b.id,1)='*'
) a where datediff(day,times,@dt) between -10 and 10
order by a.id
set nocount off
go--调用得到结果
exec p_qry '2003-11-28'go
--删除测试环境
drop table tb
drop proc p_qry/*--测试结果id names times
---- ----- ------------
001 A 2003-11-26
002 B 2003-11-20
005 E 2003-11-28(所影响的行数为 3 行)
--*/
邹老大分离字符串这一招可是用得滚瓜烂熟
PFPF
上面的test字段怎么样对应起来了
id names test times
001 A ccc 2003-11-26
002 B fff 2003-11-20
005 E ggg 2003-11-28也是一样吗
小弟真的看不懂!!!!
只能慢慢研究!!!