如果某个记录不存在,我需要做一个查询来返回下一个(或上一个)记录。例如考虑下面的表:
ID (primary key) value
1 John
3 Bob
9 Mike
10 Tom.
如果7不存在,我想查询id为7或更大的记录。
我的问题是,
- 这些类型的查询可能与SQL?
- 在数据库世界中,这样的查询被称为什么?
谢谢!
是的,这是可能的,但实现将取决于您的RDBMS。
下面是MySQL, PostgreSQL和SQLite的情况:
select ID, value
from YourTable
where id >= 7
order by id
limit 1
在MS SQL-Server, Sybase和MS- access中:
select top 1 ID, value
from YourTable
where id >= 7
order by id
在Oracle :
select * from (
select ID, value
from YourTable
where id >= 7
order by id
)
where rownum = 1
在Firebird和Informix:
select first 1 ID, value
from YourTable
where id >= 7
order by id
在DB/2中(此语法是SQL-2008标准):
select id, value
from YourTable
where id >= 7
order by id
fetch first 1 rows only
在那些有"窗口"函数的RDBMS中(SQL-2003标准):
select ID, Value
from (
select
ROW_NUMBER() OVER (ORDER BY id) as rownumber,
Id, Value
from YourTable
where id >= 7
) as tmp --- remove the "as" for Oracle
where rownumber = 1
如果你不确定你有哪个RDBMS:
select ID, value
from YourTable
where id =
( select min(id)
from YourTable
where id >= 7
)
试试MS-SQL:
SELECT TOP 1
id, value
FROM your_table
WHERE id >= 7
ORDER BY id
或MySql
SELECT id, value
FROM your_table
WHERE id >= 7
ORDER BY id
LIMIT 0,1
我将简单地这样做:
select top 1 * from myTable where id >=7
order by id
top 1
部分的实现是T-SQL (MSSQL/Sybase),其他实现各不相同,但总是可能的(mysql/postgre LIMIT 1
, oracle rownum = 1
)
select top 1 * from Persons where Id >= @Id order by Id