日期:2014-05-18  浏览次数:20616 次

请问如何实现若当天没有数据,则自动查询前一天的数据,直到有数据为止,谢谢
我想把昨天填写的内容和前天填写的内容查询出来作对比,这是我之前写的语句

select t1.author 姓名,t1.content 昨天,t2.content 前天 from table t1,jtable t2 where datediff(day,t1.date,getdate())=1 and datediff(day,t2.date,getdate())=2 and t1.issueid= t2.issueid

但是现在有个问题,就是如果我在周一和周二早上执行这条查询的话是没有结果的,因为周六日没有记录,请问这查询该如何改,可以同时实现周一查询的是上周周四,和周五的记录,周二查询的是上周五和本周一的记录,谢谢大家

------解决方案--------------------
SQL code
create table tb1
(author varchar(10),issueid int,content nvarchar(100),dt datetime)
create table tb2
(author varchar(10),issueid int,content nvarchar(100),dt datetime)

insert into tb1 values('A',1,'ABC','2012-05-26')
insert into tb1 values('B',2,'ABC','2012-05-26')
insert into tb1 values('C',3,'ABC','2012-05-26')
insert into tb1 values('B',2,'ABC','2012-05-27')
insert into tb1 values('A',1,'ABC','2012-05-27')
insert into tb1 values('b',2,'ABCe','2012-05-28')
insert into tb1 values('A',1,'ABCa','2012-05-28'
)
insert into tb2 values('A',1,'ABC','2012-05-26')
insert into tb2 values('B',2,'ABC','2012-05-26')
insert into tb2 values('C',3,'ABC','2012-05-26')
insert into tb2 values('b',2,'ABCdef','2012-05-27')
insert into tb2 values('A',1,'ABCws','2012-05-27')
insert into tb2 values('b',2,'ABC','2012-05-28')
insert into tb2 values('A',1,'ABC','2012-05-28')

select t1.author 姓名,t1.dt ,t1.content 昨天,t2.content 前天 from
  tb1 t1,tb2  t2  where t1.dt =(select MAX(dt) as date from tb1 )
 and t1.issueid=t2.issueid and datediff(day,t1.dt,t2.dt) =-1
drop table tb1
drop table tb2