阿钊的写字板

专注于 Java 技术栈与分布式系统实践，分享技术难题攻克、系统优化实战与信创项目开发经验。

达梦查询分页排序问题

问题复现

  • 环境：达梦7

查询SQL如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
id,
duty_date,
duty_leader,
duty_leader_id,
duty_week,
day_people,
day_people_id,
night_people,
night_people_id,
driver_people,
is_work,
ISNULL( ( SELECT COUNT ( id ) FROM T_OA_DUTY_PRINT_LOG WHERE is_print = '1' AND duty_id = t1.id AND created_by = 'a5051dc718b942eb876e967836ec2953' GROUP BY duty_id ), 0 ) AS PRINT_COUNT 
FROM
T_OA_DUTY_PRINT AS t1 
WHERE
1 = 1 
ORDER BY
SUBSTR ( t1.duty_date, 0, 7 ) DESC,
duty_date ASC

发现数据库的查询结果数据是正确的

但页面返回的数据排序是有问题的

问题分析

通过跟踪和调试，发现处理的逻辑拼接了以下代码

那么拼接后的SQL就是这样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT
	* 
FROM
	(
	SELECT
		rownum rn,
		id,
		duty_date,
		duty_leader,
		duty_leader_id,
		duty_week,
		day_people,
		day_people_id,
		night_people,
		night_people_id,
		driver_people,
		is_work,
		ISNULL( ( SELECT COUNT ( id ) FROM T_OA_DUTY_PRINT_LOG WHERE is_print = '1' AND duty_id = t1.id AND created_by = 'a5051dc718b942eb876e967836ec2953' GROUP BY duty_id ), 0 ) AS PRINT_COUNT 
	FROM
		T_OA_DUTY_PRINT AS t1 
	WHERE
		1 = 1 
	ORDER BY
		SUBSTR ( t1.duty_date, 0, 7 ) DESC,
		duty_date ASC 
	) template 
WHERE
	template.rn> 0 
	AND template.rn<= 20

也就是查询后的数据的ROWNUM的值并没有被重新排序，那么由根据ROWNUM进行分页，数据的排序自然就不对了。

解决方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT
	ROWNUM rn,tmp.*
FROM
	(
	SELECT
		id,
		duty_date,
		duty_leader,
		duty_leader_id,
		duty_week,
		day_people,
		day_people_id,
		night_people,
		night_people_id,
		driver_people,
		is_work,
		ISNULL( ( SELECT COUNT ( id ) FROM T_OA_DUTY_PRINT_LOG WHERE is_print = '1' AND duty_id = t1.id AND created_by = 'a5051dc718b942eb876e967836ec2953' GROUP BY duty_id ), 0 ) AS PRINT_COUNT 
	FROM
		T_OA_DUTY_PRINT AS t1 
	WHERE
		1 = 1 
	ORDER BY
		SUBSTR ( t1.duty_date, 0, 7 ) DESC,
	duty_date ASC 
	) AS tmp

image.png

参考

开发者笔记 达梦数据库