如果有标识符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)

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

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

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

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

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

你可以用 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;