查询当天记录

[zhouxuancq0912]

查询当天前10条记录，且当前不足10条时，也要显示10条记录

SQL怎么写啊。

[WaterGG]

不足10条拿什么替补？

[-晴天]

select top 10 * from tb where dt<getdate() order by dt desc

[zhouxuancq0912]

就拿这张表里的记录替补，足10条就可以

[WaterGG]

select top 10 * from tb order by case when datediff(day,dt,gedate()) then 0 else 1 end, dt desc

[-晴天]

呵呵.这是当天后十条...
前十条:

create table tb(dt datetime)
insert into tb select '2010-12-17 16:23:20'
insert into tb select '2010-12-17 17:23:20'
insert into tb select '2010-12-17 18:23:20'
insert into tb select '2010-12-17 19:23:20'
insert into tb select '2010-12-17 20:23:20'
insert into tb select '2010-12-17 21:23:20'
insert into tb select '2010-12-17 22:23:20'
insert into tb select '2010-12-17 23:23:20'
insert into tb select '2010-12-18 03:23:20'
insert into tb select '2010-12-18 05:23:20'
insert into tb select '2010-12-18 07:23:20'
insert into tb select '2010-12-18 09:23:20'
go
select top 10 * from tb
order by (case when left(dt,10)=left(getdate(),10) then datediff(ss,getdate(),dt) else datediff(ss,dt,getdate()) end)
go
drop table tb
/*
dt
-----------------------
2010-12-18 03:23:20.000
2010-12-18 05:23:20.000
2010-12-18 07:23:20.000
2010-12-18 09:23:20.000
2010-12-17 23:23:20.000
2010-12-17 22:23:20.000
2010-12-17 21:23:20.000
2010-12-17 20:23:20.000
2010-12-17 19:23:20.000
2010-12-17 18:23:20.000

(10 行受影响)
*/