我正在尝试在使用键集分页(寻求分页(进行分页时进行排序。但是,其中一个字段不是唯一的,只能为真/假 (0/1(。在键集分页中,您从一个查询中获取最后一个 id,并将其用于下一页,对吗?我将如何处理这种情况?
我从以下方面开始:
select * from foo order by admin desc, name asc limit 3
它返回
admin name
1 a
1 b
1 c
然后我应该再次执行查询,仅使用参数
select * from foo where (admin, name) > ('1', 'c') order by admin desc, name asc limit 3
但是,这什么也没返回!
我怎样才能解决这种行为,让它带来?
admin name
0 a
0 d
0 x
以下数据以获得更好的背景信息:
我有这张桌子
foo(
name varchar
admin varchar(1) (0 or 1)
);
我有这些数据
admin name
1 a
1 b
1 c
0 a
0 d
0 x
当我运行此查询时
SELECT * from foo
order by admin desc, name asc
使用此索引
b-tree(admin desc, name asc)
由于admin
是数字,因此您可以使用
WHERE (-admin, name) > (-1, 'c')
如果列中没有 NULL,这应该可以正常工作。
对于字符串键,请查看此问题及其答案。
为避免间隙,请确保在ORDER BY
和WHERE
子句中包含主键。
您可以将 Admin 从 '1', '0' 转换为布尔值,并在 NOT ADMIN::boolean 上进行排序。甚至将列从文本转换为布尔值。
-- set up
create table foo( admin text, name text);
insert into foo(admin,name)
values ( '1', 'a'), ( '1','b'),( '1','c')
, ( '0', 'a'), ('0','b'),('0','c')
, ( '1', 'g'), ( '0','d'),( '1','e')
, ( '1', 'x'), ( '0','y'),( '1','z')
, ( '1', 't'), ( '0','4'),( '1','7');
-- Function to return data for Next_Page
create or replace function next_page(in last_foo foo)
returns refcursor
language plpgsql strict
as $$
declare
ref refcursor;
begin
open ref for
select * from foo
where (not admin::boolean, name) > (not last_foo.admin::boolean, last_foo.name)
order by not admin::boolean, name
limit 3;
return ref;
end;
$$;
-- demo
select true > false, '1' > '0';
--- test, Individual selects for each page with known results
select * from foo;
select * from foo order by not admin::boolean, name;
select * from next_page();
select * from next_page(('1', 'b'));
select * from next_page(('1', 'g'));
select * from next_page(('1', 'z'));
select * from next_page(('0', 'b'));
select * from next_page(('0', 'y'));
-- test routine for continuous paging
do $$
declare
frec_cur REFCURSOR;
frec foo ;
frecp foo;
l_had_rec boolean;
l_page_num integer = 0;
begin
frecp = ('1','')::foo;
loop
l_had_rec = false;
l_page_num = l_page_num + 1;
raise notice '--- Page % --------------------------------', l_page_num;
frec_cur = next_page2(frecp);
loop
fetch frec_cur into frec;
exit when not found;
l_had_rec = true;
raise notice 'Admin: %, Name: %',frec.admin::text, frec.name;
frecp = frec;
end loop;
exit when not l_had_rec;
end loop;
raise notice '*** End of Data ***' ;
end ;
$$;