如果某个记录不存在,我需要进行查询以返回下一个(或上一个)记录.例如,请考虑下表:
- ID (primary key) value
- 1 John
- 3 Bob
- 9 Mike
- 10 Tom.
如果7不存在,我想查询id为7或更大的记录.
我的问题是,
> sql可以使用这些类型的查询吗?
>在DB世界中调用了哪些此类查询?
谢谢!
解决方法
是的,这是可能的,但实现将取决于您的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
- )