用于选择“下一个"记录的 SQL 查询(类似于第一个或前 N 个) [英] SQL Query to Select the 'Next' record (similar to First or Top N)

问题描述

如果某个记录不存在，我需要执行查询以返回下一条(或上一条)记录.例如考虑下表:

I need to do a query to return the next (or prev) record if a certain record is not present. For instance consider the following table:

ID (primary key)    value
1                    John
3                    Bob
9                    Mike
10                   Tom.

如果不存在 7，我想查询 id 为 7 或更大的记录.

I'd like to query a record that has id 7 or greater if 7 is not present.

我的问题是，

1. SQL 可以执行这些类型的查询吗?
2. 在数据库世界中，此类查询称为什么?

谢谢！

推荐答案

是的，这是可能的，但实现将取决于您的 RDBMS.

Yes, it's possible, but implementation will depend on your RDBMS.

这是在 MySQL、PostgreSQL 和 SQLite 中的样子:

Here's what it looks like in MySQL, PostgreSQL and SQLite:

select ID, value
from YourTable
where id >= 7
order by id
limit 1

在 MS SQL-Server、Sybase 和 MS-Access 中:

In MS SQL-Server, Sybase and MS-Access:

select top 1 ID, value
from YourTable
where id >= 7
order by id

在甲骨文中:

select * from (
    select ID, value
    from YourTable
    where id >= 7 
    order by id
)
where rownum = 1

在 Firebird 和 Informix 中:

In Firebird and Informix:

select first 1 ID, value
from YourTable
where id >= 7
order by id

在 DB/2 中(此语法在 SQL-2008 标准中):

In DB/2 (this syntax is in SQL-2008 standard):

select id, value
from YourTable
where id >= 7
order by id
fetch first 1 rows only

在那些具有窗口"功能的 RDBMS 中(在 SQL-2003 标准中):

In those RDBMS that have "window" functions (in SQL-2003 standard):

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:

And if you are not sure which RDBMS you have:

select ID, value
from YourTable
where id = 
      ( select min(id)
        from YourTable
        where id >= 7
      )

这篇关于用于选择“下一个"记录的 SQL 查询(类似于第一个或前 N 个)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！