oracle(四)-order by 排序分页重复解决

本文针对Oracle数据库中因order by排序不唯一导致的数据不稳定问题,通过增加主键或ROWID进行二次排序,有效解决了分页时数据重复显示的问题,确保了数据的稳定性和正确性。

问题描述: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` 排序Oracle 数据库中,可以通过多种方式利用主键进行排序操作。为了确保排序逻辑符合预期并优化性能,建议遵循特定的最佳实践。 #### 1. 基本语法结构 基本的 SQL 查询语句可以在 SELECT 后面加上 ORDER BY 子句来进行排序: ```sql SELECT * FROM table_name ORDER BY column_name ASC|DESC; ``` 当涉及到主键时,可以直接指定主键作为排序依据[^3]。 #### 2. 结合子查询处理复杂排序需求 对于更复杂的场景,比如希望先按某个字段排序再按主键排序,则可以采用嵌套查询的方式。这种方法特别适用于需要分页的情况,在早期版本(如 Oracle 12c 及以前),由于 ROWNUM 的特性,通常会这样做以确保结果集顺序正确。 示例代码如下所示: ```sql WITH sorted_data AS ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY custom_column, primary_key) rn FROM your_table t ) SELECT * FROM sorted_data WHERE rn BETWEEN :start AND :end; ``` 这里 `custom_column` 表示用户想要额外考虑的一个或多个列名;而 `primary_key` 则代表实际使用的主键名称。此方法不仅能够满足多条件排序的需求,而且还能很好地支持分页功能。 #### 3. 性能考量与索引使用 关于 `ORDER BY` 是否会触发索引扫描的问题,这取决于具体的表设计以及所涉及的数据量等因素。如果存在针对被排序字段建立的有效索引,则很可能提高执行效率。然而需要注意的是,并不是所有的 `ORDER BY` 都一定会命中索引——特别是当排序表达式较为复杂或是包含了函数调用等情况时[^4]。 例如,假设有一个名为 EMP 的员工信息表,并且已经为主键创建了一个 B-tree 类型的索引,那么基于该主键做升序/降序排列的操作就有可能借助到这个索引来加速检索过程。 ```sql -- 查看EMP表上的索引及其对应的列 SELECT INDEX_NAME, COLUMN_NAME FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'EMP'; ``` 上述命令可以帮助确认当前环境中是否存在有助于提升排序速度的相关索引配置。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值