问题复现
查询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
|
参考