Sql案例整理-排序分页BUG

排序分页BUG

测试数据

/*
 Navicat Premium Data Transfer

 Source Server         : 本地
 Source Server Type    : PostgreSQL
 Source Server Version : 120007
 Source Host           : localhost:5432
 Source Catalog        : test
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 120007
 File Encoding         : 65001

 Date: 08/09/2021 08:28:33
*/


-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS "public"."t_user";
CREATE TABLE "public"."t_user" (
  "id" int4 NOT NULL,
  "age" int4,
  "name" varchar(50) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO "public"."t_user" VALUES (1, 20, 'li1');
INSERT INTO "public"."t_user" VALUES (2, 20, 'li2');
INSERT INTO "public"."t_user" VALUES (3, 20, 'li3');
INSERT INTO "public"."t_user" VALUES (4, 20, 'li4');
INSERT INTO "public"."t_user" VALUES (5, 20, 'li5');
INSERT INTO "public"."t_user" VALUES (6, 20, 'li6');
INSERT INTO "public"."t_user" VALUES (7, 20, 'li7');
INSERT INTO "public"."t_user" VALUES (8, 20, 'li8');
INSERT INTO "public"."t_user" VALUES (9, 20, 'li9');
INSERT INTO "public"."t_user" VALUES (10, 20, 'li10');
INSERT INTO "public"."t_user" VALUES (11, 20, 'li11');
INSERT INTO "public"."t_user" VALUES (12, 20, 'li12');
INSERT INTO "public"."t_user" VALUES (13, 20, 'li13');
INSERT INTO "public"."t_user" VALUES (14, 20, 'li14');
INSERT INTO "public"."t_user" VALUES (15, 20, 'li15');
INSERT INTO "public"."t_user" VALUES (16, 20, 'li16');
INSERT INTO "public"."t_user" VALUES (17, 20, 'li17');
INSERT INTO "public"."t_user" VALUES (18, 20, 'li18');
INSERT INTO "public"."t_user" VALUES (19, 20, 'li19');
INSERT INTO "public"."t_user" VALUES (20, 20, 'li20');
INSERT INTO "public"."t_user" VALUES (21, 20, 'li21');
INSERT INTO "public"."t_user" VALUES (22, 20, 'li22');
INSERT INTO "public"."t_user" VALUES (23, 20, 'li23');
INSERT INTO "public"."t_user" VALUES (24, 20, 'li24');
INSERT INTO "public"."t_user" VALUES (25, 20, 'li25');
INSERT INTO "public"."t_user" VALUES (26, 20, 'li26');
INSERT INTO "public"."t_user" VALUES (27, 20, 'li27');
INSERT INTO "public"."t_user" VALUES (28, 20, 'li28');
INSERT INTO "public"."t_user" VALUES (29, 20, 'li29');
INSERT INTO "public"."t_user" VALUES (30, 20, 'li30');
INSERT INTO "public"."t_user" VALUES (31, 20, 'li31');
INSERT INTO "public"."t_user" VALUES (32, 20, 'li32');
INSERT INTO "public"."t_user" VALUES (33, 20, 'li33');
INSERT INTO "public"."t_user" VALUES (34, 20, 'li34');
INSERT INTO "public"."t_user" VALUES (35, 20, 'li35');
INSERT INTO "public"."t_user" VALUES (36, 20, 'li36');
INSERT INTO "public"."t_user" VALUES (37, 20, 'li37');
INSERT INTO "public"."t_user" VALUES (38, 20, 'li38');
INSERT INTO "public"."t_user" VALUES (39, 20, 'li39');
INSERT INTO "public"."t_user" VALUES (40, 20, 'li40');
INSERT INTO "public"."t_user" VALUES (41, 20, 'li41');
INSERT INTO "public"."t_user" VALUES (42, 20, 'li42');
INSERT INTO "public"."t_user" VALUES (43, 20, 'li43');
INSERT INTO "public"."t_user" VALUES (44, 20, 'li44');
INSERT INTO "public"."t_user" VALUES (45, 20, 'li45');
INSERT INTO "public"."t_user" VALUES (46, 20, 'li46');
INSERT INTO "public"."t_user" VALUES (47, 20, 'li47');
INSERT INTO "public"."t_user" VALUES (48, 20, 'li48');
INSERT INTO "public"."t_user" VALUES (49, 20, 'li49');
INSERT INTO "public"."t_user" VALUES (50, 20, 'li50');
INSERT INTO "public"."t_user" VALUES (51, 20, 'li51');
INSERT INTO "public"."t_user" VALUES (52, 20, 'li52');
INSERT INTO "public"."t_user" VALUES (53, 20, 'li53');
INSERT INTO "public"."t_user" VALUES (54, 20, 'li54');
INSERT INTO "public"."t_user" VALUES (55, 20, 'li55');
INSERT INTO "public"."t_user" VALUES (56, 20, 'li56');
INSERT INTO "public"."t_user" VALUES (57, 20, 'li57');
INSERT INTO "public"."t_user" VALUES (58, 20, 'li58');
INSERT INTO "public"."t_user" VALUES (59, 20, 'li59');
INSERT INTO "public"."t_user" VALUES (60, 20, 'li60');
INSERT INTO "public"."t_user" VALUES (61, 20, 'li61');
INSERT INTO "public"."t_user" VALUES (62, 20, 'li62');
INSERT INTO "public"."t_user" VALUES (63, 20, 'li63');
INSERT INTO "public"."t_user" VALUES (64, 20, 'li64');
INSERT INTO "public"."t_user" VALUES (65, 20, 'li65');
INSERT INTO "public"."t_user" VALUES (66, 20, 'li66');
INSERT INTO "public"."t_user" VALUES (67, 20, 'li67');
INSERT INTO "public"."t_user" VALUES (68, 20, 'li68');
INSERT INTO "public"."t_user" VALUES (69, 20, 'li69');
INSERT INTO "public"."t_user" VALUES (70, 20, 'li70');
INSERT INTO "public"."t_user" VALUES (71, 20, 'li71');
INSERT INTO "public"."t_user" VALUES (72, 20, 'li72');
INSERT INTO "public"."t_user" VALUES (73, 20, 'li73');
INSERT INTO "public"."t_user" VALUES (74, 20, 'li74');
INSERT INTO "public"."t_user" VALUES (75, 20, 'li75');
INSERT INTO "public"."t_user" VALUES (76, 20, 'li76');
INSERT INTO "public"."t_user" VALUES (77, 20, 'li77');
INSERT INTO "public"."t_user" VALUES (78, 20, 'li78');
INSERT INTO "public"."t_user" VALUES (79, 20, 'li79');
INSERT INTO "public"."t_user" VALUES (80, 20, 'li80');
INSERT INTO "public"."t_user" VALUES (81, 20, 'li81');
INSERT INTO "public"."t_user" VALUES (82, 20, 'li82');
INSERT INTO "public"."t_user" VALUES (83, 20, 'li83');
INSERT INTO "public"."t_user" VALUES (84, 20, 'li84');
INSERT INTO "public"."t_user" VALUES (85, 20, 'li85');
INSERT INTO "public"."t_user" VALUES (86, 20, 'li86');
INSERT INTO "public"."t_user" VALUES (87, 20, 'li87');
INSERT INTO "public"."t_user" VALUES (88, 20, 'li88');
INSERT INTO "public"."t_user" VALUES (89, 20, 'li89');
INSERT INTO "public"."t_user" VALUES (90, 20, 'li90');
INSERT INTO "public"."t_user" VALUES (91, 20, 'li91');
INSERT INTO "public"."t_user" VALUES (92, 20, 'li92');
INSERT INTO "public"."t_user" VALUES (93, 20, 'li93');
INSERT INTO "public"."t_user" VALUES (94, 20, 'li94');
INSERT INTO "public"."t_user" VALUES (95, 20, 'li95');
INSERT INTO "public"."t_user" VALUES (96, 20, 'li96');
INSERT INTO "public"."t_user" VALUES (97, 20, 'li97');
INSERT INTO "public"."t_user" VALUES (98, 20, 'li98');
INSERT INTO "public"."t_user" VALUES (99, 20, 'li99');
INSERT INTO "public"."t_user" VALUES (100, 20, 'li100');

-- ----------------------------
-- Primary Key structure for table t_user
-- ----------------------------
ALTER TABLE "public"."t_user" ADD CONSTRAINT "t_user_pkey" PRIMARY KEY ("id");

查询Sql

SELECT * FROM "t_user" ORDER BY age asc limit 5 offset 0;
SELECT * FROM "t_user" ORDER BY age asc limit 5 offset 5;
SELECT * FROM "t_user" ORDER BY age asc limit 5 offset 10;
SELECT * FROM "t_user" ORDER BY age asc limit 5 offset 15;
SELECT * FROM "t_user" ORDER BY age asc limit 5 offset 20;

查询结果

原因

排序字段值重复，值相同的时候排序随机

解决办法

在后面添加主键排序(如：id)，固定排序

SELECT * FROM "t_user" ORDER BY age asc,"id" limit 5 offset 0;
SELECT * FROM "t_user" ORDER BY age asc,"id" limit 5 offset 5;
SELECT * FROM "t_user" ORDER BY age asc,"id" limit 5 offset 10;
SELECT * FROM "t_user" ORDER BY age asc,"id" limit 5 offset 15;
SELECT * FROM "t_user" ORDER BY age asc,"id" limit 5 offset 20;