在SQL中查找序列的空缺而无需创建其他表 [英] Find gaps of a sequence in SQL without creating additional tables

问题描述

我有一个表 invoices 和一个字段 invoice_number .当我执行从发票中选择发票编号时，会发生这种情况:

I have a table invoices with a field invoice_number. This is what happens when i execute select invoice_number from invoice:

invoice_number
--------------
1
2
3
5
6
10
11

我想要一个能给我以下结果的SQL:

I want a SQL that gives me the following result:

gap_start | gap_end
4         | 4
7         | 9

我该如何编写SQL来执行此类查询?我正在使用PostgreSQL.

How can i write a SQL to perform such query? I am using PostgreSQL.

推荐答案

使用现代SQL，可以使用窗口函数轻松实现:

With modern SQL, this can easily be done using window functions:

select invoice_number + 1 as gap_start, 
       next_nr - 1 as gap_end
from (
  select invoice_number, 
         lead(invoice_number) over (order by invoice_number) as next_nr
  from invoices
) nr
where invoice_number + 1 <> next_nr;

SQLFiddle: http://sqlfiddle.com/#!15/1e807/1

SQLFiddle: http://sqlfiddle.com/#!15/1e807/1

这篇关于在SQL中查找序列的空缺而无需创建其他表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！