解决数据库排序后使用分页数据不匹配

原因：在排序字段有数据重复的情况下，会很容易出现排序结果与预期不一致的问题

错误列子

SELECT
	t1.gcmc,
	t2."name" AS distname,
	( SELECT NAME FROM xj_company c WHERE c.uuid = t1.company_id ) company 
FROM
	xj_proj t1
	LEFT JOIN xj_code_dist t2 ON t1.dist_id = t2.uuid
	LEFT JOIN xj_company c ON c.uuid = t1.company_id 
WHERE
	1 = 1 
ORDER BY
	t2."name"  DESC

上面是没有分页的数据

分页之后

SELECT
	t1.gcmc,
	t2."name" AS distname,
	( SELECT NAME FROM xj_company c WHERE c.uuid = t1.company_id ) company 
FROM
	xj_proj t1
	LEFT JOIN xj_code_dist t2 ON t1.dist_id = t2.uuid
	LEFT JOIN xj_company c ON c.uuid = t1.company_id 
WHERE
	1 = 1 
ORDER BY
	t2."name"  DESC 
	LIMIT 20 OFFSET 0

原因：因为我的t2.“name” 是一样的，在排序字段有数据重复的情况下，会很容易出现排序结果与预期不一致的问题

解决方案，给排序字段添加自增字段,对该字段进行降序或升序
使用ROW_NUMBER ( ) OVER ()函数

SELECT
	t1.gcmc,
	t2."name" AS distname,
	ROW_NUMBER ( ) OVER ( ORDER BY t2."name" ASC ) AS area,
	( SELECT NAME FROM xj_company c WHERE c.uuid = t1.company_id ) company 
FROM
	xj_proj t1
	LEFT JOIN xj_code_dist t2 ON t1.dist_id = t2.uuid
	LEFT JOIN xj_company c ON c.uuid = t1.company_id 
WHERE
	1 = 1 
ORDER BY
	area DESC 
	LIMIT 20 OFFSET 0
	

现在在对比结果就是一样的了