问题复现
查询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
Copy |
发现数据库的查询结果数据是正确的
但页面返回的数据排序是有问题的
问题分析
通过跟踪和调试，发现处理的逻辑拼接了以下代码
那么拼接后的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
Copy |
也就是查询后的数据的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
Copy |
参考
