如何在 postgreSQL 中获取 20 个连续的数字行 [英] How to fetch 20 continue number row in postgreSQL

问题描述

我有这样的数据库:

id  | identifier  | status | content
---------------------------------------
1   |   10       |   AV       | text
2   |   11       |    AV      |  book
3   |   12       |     WK      |  table
4   |    15      |      WK     | test
...

我想获得 20 行，其中所有标识符都应该继续并且状态应该是AV"?怎么做?

I want to get the 20 rows in which all identifier should be continued and status should be 'AV'? How to do it?

我需要标识符像1,2,3,4,5,6....一样连续，所以像1,3,4,5这样的标识符不会被选中.

I need the identifier is continued like 1,2,3,4,5,6...., so identifier like 1,3,4,5 will not be selected.

我期望的结果是:

id   | identifier |  status  
any  |   101      |    AV
any  |   102      |    AV
any  |   103      |    AV
...
any  |   120      |    AV 

推荐答案

如果有标识符 1..20 的行，那么我想您可能正在寻找:

If there are rows for identifiers 1..20 then I think you may be looking for:

SELECT * FROM tableA 
WHERE status = 'AV'
ORDER BY identifier
LIMIT 20

如果缺少标识符，您可以左/右加入一个数字序列:

If there are missing identifiers you could left/right join with a sequence of numbers:

SELECT Numbers.N, Table1.* FROM Table1 
RIGHT JOIN (SELECT N FROM generate_series(1, 20) N) as Numbers
ON Table1.identifier = Numbers.N AND Status = 'AV'
ORDER BY Numbers.N
LIMIT 20

小提琴这里

为了

CREATE TABLE TABLE1(
   ID INT PRIMARY KEY     NOT NULL,
   IDENTIFIER     INT     NOT NULL,
   NAME           TEXT    NOT NULL,
   STATUS           TEXT    NOT NULL
   
);
INSERT INTO TABLE1 VALUES(11123, 1,  'A', 'AV');
INSERT INTO TABLE1 VALUES(22312, 2,  'B', 'ZB');
INSERT INTO TABLE1 VALUES(1323, 3, 'C', 'AV');

结果是

n   id  identifier  name    status
1   11123   1   A   AV
2   (null)  (null)  (null)  (null)
3   1323    3   C   AV
4   (null)  (null)  (null)  (null)
5   (null)  (null)  (null)  (null)

这篇关于如何在 postgreSQL 中获取 20 个连续的数字行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！