当前位置: 代码迷 >> Sql Server >> 请教怎么实现若当天没有数据,则自动查询前一天的数据,直到有数据为止,多谢
  详细解决方案

请教怎么实现若当天没有数据,则自动查询前一天的数据,直到有数据为止,多谢

热度:78   发布时间:2016-04-27 12:30:18.0
请问如何实现若当天没有数据,则自动查询前一天的数据,直到有数据为止,谢谢
我想把昨天填写的内容和前天填写的内容查询出来作对比,这是我之前写的语句

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) =-1drop table tb1drop table tb2
  相关解决方案