如何在postgresql中获取20个连续数字行

roejwanj  于 2021-07-26  发布在  Java
关注(0)|答案(5)|浏览(305)

我有如下数据库:

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

我想得到20行中的所有标识符应继续和状态应为'av'?怎么做?
我需要标识符像1,2,3,4,5,6….一样继续,所以像1,3,4,5这样的标识符将不会被选中。
我希望结果是:

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

c9x0cxw01#

如果有标识符1..20的行,那么我认为您可能正在查找:

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

如果缺少标识符,可以用数字序列左/右连接:

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)
z0qdvdin

z0qdvdin2#

使用限制关键字
您的查询应如下所示:

SELECT TN.*
FROM YouTableName AS TN
ORDER BY TN.identifier ASC   -- or DESC depending of order wanted
LIMIT 20
ee7vknir

ee7vknir3#

您的查询应与此查询相同:

select * 
from tableA 
where status = 'AV'
order by identifier
limit 20
``` `order by` 正在使用 `asc` 作为默认值,因此不需要设置 `asc` 用于此查询。
gt0wga4j

gt0wga4j4#

select * from (
Select T1.* from Tablename T1
inner join tablename T2 on (T1.identifier) = (T2.identifier -1)    
where status = 'AV'
union
Select T2.* from Tablename T1
inner join tablename T2 on (T1.identifier) = (T2.identifier -1)  
where status = 'AV'
) Res limit 20
a2mppw5e

a2mppw5e5#

你可以用 lag() 要轻松获得第20名:

select t.*
from (select t.*,
             lag(identifier, 19) over (partition by status order by identifier) as prev19_status_identifier
      from t
     ) t
where prev19_status_identifer = identifier - 19 and
      status = 'AV';

如果需要所有这些行,可以将其视为间隙和孤岛,使用:

select t.*
from (select t.*,
             count(*) over (partition by status, identifier - seqnum) as cnt
      from (select t.*,
                   row_number() over (partition by status order by identifier) as seqnum
            from t
            where status = 'AV'
           ) t
     ) t
where cnt >= 20;

相关问题