问题复现

  • 环境:达梦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

参考