--创建一个函数
create function f_tb(@mydate datetime)
returns varchar(1000)
as
begin
declare @a varchar(1000)
set @a=''
select @a=@a+','+xx1+','+xx2 from tb where mydate=@mydate or mydate=dateadd(d,-1,@mydate)
return(stuff(@a,1,1,''))
end
--查询
select mydate,dbo.f_tb(mydate) from tb where mydate='2004-10-3'
create function f_tb(@mydate datetime)
returns varchar(1000)
as
begin
declare @a varchar(1000)
set @a=''
select @a=@a+','+xx1+','+xx2 from tb where mydate=@mydate or mydate=dateadd(d,-1,@mydate)
return(stuff(@a,1,1,''))
end
--查询
select mydate,dbo.f_tb(mydate) from tb where mydate='2004-10-3'
create function f_tb(@mydate datetime)
returns varchar(1000)
as
begin
declare @a varchar(1000)
set @a=''
select @a=@a+','+xx1+','+xx2 from tb where mydate=@mydate or mydate=dateadd(d,-1,@mydate) order by mydate desc
return(stuff(@a,1,1,''))
endselect mydate,结果=dbo.f_tb(mydate) from tb where mydate='2004-10-3'
FROM TABLE4 a LEFT OUTER JOIN
TABLE4 b ON b.mydate + 1 = a.mydate
WHERE (a.mydate = '2004/10/3')輸出結果:
2004/10/3 cccc cccc1 bbbb bbb1
insert into tb
select '2004-10-1','aaa','aaa1' union all
select '2004-10-2','bbb','bbb1' union all
select '2004-10-3','ccc','ccc1' --查询
select a.*
,zxx1=(select top 1 xx1 from tb where datediff(day,mydate,a.mydate)=1 )
,zxx2=(select top 1 xx2 from tb where datediff(day,mydate,a.mydate)=1)
from
(select * from tb where mydate='2004-10-3')a--删除表
drop table tb--结果:
mydate xx1 xx2 zxx1 zxx2
------------------------------------------------------ ----- ----- ----- -----
2004-10-03 00:00:00.000 ccc ccc1 bbb bbb1
--测试:
create table tb(mydate datetime,xx1 varchar(5),xx2 varchar(5))
insert into tb
select '2004-10-1','aaa','aaa1' union all
select '2004-10-2','bbb','bbb1' union all
select '2004-10-3','ccc','ccc1'
--查询
select mydate=convert(varchar(10),a.mydate,120),a.xx1,a.xx2
,zxx1=(select top 1 xx1 from tb where datediff(day,mydate,a.mydate)=1 )
,zxx2=(select top 1 xx2 from tb where datediff(day,mydate,a.mydate)=1)
from
(select * from tb where mydate='2004-10-3')a--删除表
drop table tb--运行结果:
mydate xx1 xx2 zxx1 zxx2
---------- ----- ----- ----- -----
2004-10-03 ccc ccc1 bbb bbb1(所影响的行数为 1 行)