求与当前时间一前一后的记录的sql

[zimu312500]

ID TIME
1 6:00
2 7:00
3 8:00

如果现在是7:30，则返回第2,3条记录
请不使用union all语句哦，非常感谢！

[zimu312500]

最好用mysql的语法哦。。。

[-晴天]

create table tb(id int,[time] time)
insert into tb select 1,'6:00'
insert into tb select 2,'7:00'
insert into tb select 3,'8:00'
insert into tb select 4,'9:00'
go
declare @s time
set @s='7:30'
select * from tb a 
where time>@s and not exists(select 1 from tb where time<a.time and time>@s)
or time<@s and not exists(select 1 from tb where time>a.time and time<@s)
/*
id          time
----------- ----------------
2           07:00:00.0000000
3           08:00:00.0000000

(2 行受影响)
*/
go
drop table tb

[dawugui]

--如果是sql server则如下:

select top 1 * from tb where time < '7:30' order by time desc
select top 1 * from tb where time > '7:30' order by time 

[-晴天]

上面的查询语句也能用于mysql.

[zimu312500]

[Quote=引用 3 楼 dawugui 的回复:]

--如果是sql server则如下:

SQL code
select top 1 * from tb where time < '7:30' order by time desc
select top 1 * from tb where time > '7:30' order by time
[/Quote]
大乌龟大大的用了union all了，不过用这样可能效率反而会比晴天写的会高一些哦，哈哈

[-晴天]

据说不要用 union all 的...