oracle（四）-order by 排序分页重复解决数据库

问题描述：oracle数据库order by排序不唯一，会导致最终的数据排序是不是稳定的，表现出来的结果，就是分页时会重复显示；症状如下：第2页和第3页有重复的数据。

有问题的sql：

select * from ( select u.user_id user_id, u.user_name user_name, u.mobile1 mobile, substr(u.id_no, -4) id_no, i.industry_name industry_name, to_char(su.organ_type) organ_type, su.organ_name group_name, su.train_date train_date, 0 assign_status, '未分配' assign_status_name from szfdc.szfdc_users su join sec_users u on u.user_id = su.id join sec_user_group_rel ug on u.user_id = ug.user_id left join t_industry i on i.organ_type = su.organ_type where ( su.organ_type in ( 512, 1024,2048 ) or su.organ_type is null ) and ug.group_id = 10000000313 order by su.train_date desc nulls last) where rownum <= 20;

问题解决：建议在 order by 最后，增加 PK 列或 ROWID；
修改后的sql：在order的后面多加了一个u.user_id（主键）进行排序

select * from ( select u.user_id user_id, u.user_name user_name, u.mobile1 mobile, substr(u.id_no, -4) id_no, i.industry_name industry_name, to_char(su.organ_type) organ_type, su.organ_name group_name, su.train_date train_date, 0 assign_status, '未分配' assign_status_name from szfdc.szfdc_users su join sec_users u on u.user_id = su.id join sec_user_group_rel ug on u.user_id = ug.user_id left join t_industry i on i.organ_type = su.organ_type where ( su.organ_type in ( 512, 1024,2048 ) or su.organ_type is null ) and ug.group_id = 10000000313 order by su.train_date desc nulls last,u.user_id ) where rownum <= 20;

【oracle（四）-order by 排序分页重复解决】总结：以后只要遇到排序列可能存在重复的情况下，都多加上一个主键列进行排序