解决oracle根据字段类型为字符串(中间包含数字)按顺序排序问题

最新推荐文章于 2022-12-03 22:33:33 发布
原创 最新推荐文章于 2022-12-03 22:33:33 发布 · 333 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库

本文介绍了一种使用SQL进行复杂排序的方法，包括如何利用正则表达式和数字转换来实现多条件排序。此外，还提供了HQL和SQL中进行类型转换的实用语法。

select p.attribute1_,p.attribute2_,p.* from app_pay_payment p order by p.attribute1_,regexp_substr(p.attribute2_,'[^0-9]+'),to_number(regexp_substr(p.attribute2_,'[0-9]+'))

 

上面方法可能有些复杂 ,也可以直接用下面方法

 

HQL写法:cast(对象属性 as long)

SQL写法:cast(字段名 as number)

ORACLE一般排名、百分比排名等排序函数详解
qq_43605229的博客
07-06 5335
在序列 1 2 2 3 3 4 4 5 中，值 3 的排名为 4，因为它是第四个不同的值。当有多行具有相同的值时，RANK 函数会返回相同的排名。其中，排名指的是值在序列中的排名（从1开始），总数指的是序列中元素的总数。其中，排名指的是值在序列中的排名（从1开始），总数指的是序列中元素的总数。与 RANK 和 DENSE_RANK 函数不同，ROW_NUMBER 函数不会为并列排名的行返回相同的数字。RANK 和 DENSE_RANK 都是 Oracle 中的分析函数，它们用于计算一组值中某个值的排名。
hive实现regexp_substr()
along
11-09 7667
首先需要清楚regexp_substr()的作用，先看官方解释REGEXP_SUBSTR通过允许您在字符串中搜索正则表达式模式来扩展功能的功能。它也与相似REGEXP_INSTR，但是它不返回子字符串的位置，而是返回子字符串本身。如果您需要匹配字符串的内容，而不是它在源字符串中的位置，则此功能很有用。该函数返回的字符串形式为VARCHAR2或，并且CLOB数据的字符集与相同source_char。 接下来对函数中的各个参数进行说明： REGEXP_SUBSTR(source_char, pattern, p
oracle含有数字的字符串排序
兔老大的胡萝卜
01-02 660
含有数字的字符串经常将首数字较小的排在前面，现在按照下面的办法则可避免 select * from tbname order by length(NAME),NAME;
[1173]regexp_replace()、regexp_substr()、regexp_instr()函数的用法
热门推荐
周小董
12-03 1万+
将source字符串中匹配pattern的子串替换成指定字符串后返回，当输入source, pattern, occurrence参数为NULL时返回NULL，若replace_string为NULL且pattern有匹配，返回NULL，replace_string为NULL但pattern不匹配，则返回原串。1、用替换字符串中的所有数字 结果：用替换字符串中的数字0、9 结果：2、遇到非小写字母或者数字跳过，从匹配到的第4个值开始替换，替换为 结果： 结果： 结果：遇到非小写字母或者非数字跳过，将所有匹配
oracle 字符串排序
weixin_30650859的博客
07-06 572
WITH temp0 AS (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 100) SELECT MAX(REPLACE(sys_connect_by_path(str, ' '), ' ', '')) f1 FROM (SELECT str, row_number() over(ORDER BY ascii(str) ASC) ...
Oracle同一字段有数字和字母时进行排序
csdndz的博客
07-15 3162
需求：对如下数据排序： 思路一：将字母转成ASCII码值进行排序 (最开始想到的这个思路，但该方法待优化！！！) ORDER BY CASE WHEN ASCII(字段) < 58 THEN TO_NUMBER(字段) ELSE ASCII(字段) END [0-9]的ASCII码值为[48-57] [A-Z]的ASCII码值为[65-90] [a-z]的ASCII码值为[97-122] 这里直接判断小于58就认为是数字，数字时取数值，数字大于65时就会和字母冲突，当前业务中数字最大为20（无
解决Oracle字符串中包含数字、特殊符号的排序问题
09-09
通过理解并巧妙运用`TRANSLATE`函数，我们可以解决Oracle数据库中字符串包含数字和特殊符号的排序问题，从而确保数据按预期的逻辑进行排序，满足项目需求。在实际应用中，可能需要根据具体的数据结构和排序规则调整`...
oracle通过sql实现字符串转数字oracle函数
06-23
最后，当我们谈论“字符串的最终排序”时，使用转换后的数字字段进行排序将更准确，因为字符串按照字典顺序排序，而数字则按照数值大小排序。例如： ```sql SELECT * FROM your_table ORDER BY TO_NUMBER(str_...
Oracle数据库中对null值的排序及mull与空字符串的区别
01-19
在对业务数据排序时候，发现有些字段的记录是null值，这时排序便出现了有违我们使用习惯的数据大小顺序问题。在Oracle中规定，在Order by排序时缺省认为null是最大值，所以如果是ASC升序则被排在最后，而DESC降序则...
Oracle中SQL语句连接字符串的符号使用介绍
12-15
- Oracle在处理字符串中包含数字和特殊符号时，会按照字典顺序排序，这可能与直观预期不同。要根据数字部分排序，可能需要使用 `TO_NUMBER` 或其他转换函数。 8. **与其他数据库系统的比较**： - Oracle的 `||` ...
oracle排序值分析函数
weixin_43970560的博客
03-27 400
-- rank() -- dense_rank() -- 语法 RANK ( ) OVER ( [query_partition_clause] order_by_clause ) -- dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause ) -- 功能 聚合函数rank和dense_rank 主要的功能是计算一组...
Oracle varchar2类型，值为数字时不转换类型的排序方法
qq_31391571的博客
01-13 730
图中字段为varchar2类型字段名num，需要排序。由于存在值为非数字类型的情况，不能使用to_number(num)。 2.修改如下，1.先按length(num)排。长度为1时的顺序不符合要求。 3.再按排一次，按num字符排。 ORDER BY length(num),num ...
oracle"字符串型数字"排序问题
叮咚侠的博客
03-29 2587
原文链接：http://blog.sina.com.cn/s/blog_58f884cf0100oa0h.html SQL> select * from t; ADDRESS -------------------------------------------------------------------------------- 4269   城南路1#  永康市
oracle取中间排名的内容,Oracle数据库中关于利用查询直接排名的方法
weixin_27828313的博客
04-09 342
首先，来构造一些数据 drop table test;create table test( name varchar2(10), account number(5)); insert into首先，，来构造一些数据drop table test;create table test(name varchar2(10),account number(5));insert into test value...
Oracle中对字符串类型的数字正确排序
CoffeMilk的博客
09-27 1万+
一、使用【ORDER BY 字段 ASC】对字符串类型数字排序得不到想要的结果 SELECT 字段名称 FROM 表名称 ORDER BY 字段名称 ASC 二、问题分析 因为排序的字段是字符串类型，所以【排序时字符从左向右依次比较】 三、解决方法 ①使用Oracle中的【CAST】函数将字段转为数字类型然后再排序即可 //CAST函数转换字段类型 CAST(字段名称 AS 数字类型) //比如：将Varchar字段BINN转为INTEGER类型 CAST(BINN AS INTEG.
Oracle 数据库中 字符型字段 按数字排序
u013456370的专栏
08-21 2137
由于需要维护表里面的值，id主键是字符串型，保存的都是数字，每次都要看好久，才知道新增id，用哪个数字； 遇到了一个主键排序的问题。字符型的主键，保存的都是数字，数据导过来以后发现数据排序都是乱的，就想着按数字规则排序。 但发现to_number总是报错，就想着里面应该是有字符存在。后来使用了正则关系式，问题解决。 以下是正则关系式的两种用法，记录下来： 方法一： select * fr...
hql regexp
学而不思则罔 思而不学则殆
07-02 2693
今天出了一个小错误，
Hibernate的HQL使用Oracle的Regexp_like函数的方式
zuihoudebingwen的专栏
11-07 1170
from  :   http://blog.youkuaiyun.com/leiyong0326/article/details/46564427 Hibernate的HQL使用Oracle的Regexp_like函数的方式 1.首先自定义一个Oracle方言的类,继承hibernate的方言类,并注册regexp_like函数,使用时请注意将hibernate的配置文件的dialect配
orderDetail.itemNo是个字符串 这样不好使 不能排序 需要转化为数字 oracle
最新发布
10-21
你遇到的问题非常典型： > `orderDetail.itemNo` 是 **字符串类型（String）**，但存储的是数字（如 `"1"`, `"2"`, ..., `"10"`）， > 直接用 `orderDetail.itemNo asc` 排序会按字典序排，结果是： > > ``` > 1, 10, 11, ..., 2, 20, 3, ... > ``` > > ❌ 不符合业务逻辑！ 而你需要的是 **数值排序（Numerical Sort）**，即： ``` 1, 2, 3, ..., 10, 11, 20 ``` 由于你在使用 Oracle 数据库，并且字段是字符串，**必须在数据库层面调用 `TO_NUMBER()` 函数进行转换排序**。 --- ## ✅ 解决方案：改用 **原生 SQL 查询（Native SQL）** 因为 HQL **不支持直接调用数据库函数如 `TO_NUMBER()`**，除非你扩展方言或使用 `@Formula`，但这些方式复杂且易出错。 所以最简单、最可靠的方式是：👉 **切换为 Native SQL 查询** --- ### ✅ 正确代码实现（使用 Hibernate 原生 SQL） ```java String nativeSql = "SELECT od.* " + "FROM ORDER_DETAIL od " + "INNER JOIN ORDERS o ON od.ORDER_NO = o.ORDER_NO AND od.CREATED_BY_COMPANY = o.CREATED_BY_COMPANY " + "WHERE od.DEL_FLAG = 0 " + " AND od.ORDER_NO != 'New' " + " AND o.DEL_FLAG = 0 " + " AND o.RELEASE_STATUS = 'Close' " + "ORDER BY o.CREATED_DATE DESC, TO_NUMBER(od.ORDER_ITEM_INDEX) ASC"; Query query = getSession().createSQLQuery(nativeSql) .addEntity(OrderDetail.class); // 映射结果到 OrderDetail 实体 List<OrderDetail> result = query.list(); ``` --- ### 🔍 关键说明 | 部分 | 说明 | |------|------| | `INNER JOIN ORDERS o` | 替代原来的 `EXISTS` 子查询，更高效也更容易写排序逻辑 | | `ON od.ORDER_NO = o.ORDER_NO AND ...` | 多条件关联，确保公司隔离正确 | | `TO_NUMBER(od.ORDER_ITEM_INDEX)` | Oracle 函数，将字符串转为数字排序 ✅ | | `o.CREATED_DATE DESC` | 主订单创建时间倒序 —— 最新订单整体靠前 | | `.addEntity(OrderDetail.class)` | 自动把查询结果映射成你的实体对象 | --- ## ✅ 注意事项 ### 1. 表名和列名大小写问题（Oracle） Oracle 默认表名/列名是 **大写的**。如果你的建表语句用了双引号小写，则需调整。 ✅ 确保： - 表名：`ORDER_DETAIL`, `ORDERS` - 列名：`ORDER_ITEM_INDEX`, `CREATED_DATE`, `DEL_FLAG`, `RELEASE_STATUS`, `ORDER_NO`, `CREATED_BY_COMPANY` ### 2. 如果表名是小写的（比如通过 JPA 自动生成），要用双引号： ```sql "order_detail" -> "\"order_detail\"" ``` 但一般生产环境都是大写，无需加引号。 --- ## ✅ 可选增强：带参数查询（防注入） 如果你有搜索条件（如 orderNo 模糊查询），可以加上参数： ```java String nativeSql = "SELECT od.* " + "FROM ORDER_DETAIL od " + "INNER JOIN ORDERS o ON od.ORDER_NO = o.ORDER_NO AND od.CREATED_BY_COMPANY = o.CREATED_BY_COMPANY " + "WHERE od.DEL_FLAG = 0 " + " AND od.ORDER_NO != 'New' " + " AND o.DEL_FLAG = 0 " + " AND o.RELEASE_STATUS = 'Close' " + " AND (:orderNo IS NULL OR od.ORDER_NO LIKE :orderNo) " + "ORDER BY o.CREATED_DATE DESC, TO_NUMBER(od.ORDER_ITEM_INDEX) ASC"; Query query = getSession().createSQLQuery(nativeSql) .addEntity(OrderDetail.class); if (StringUtils.isNotBlank(searchOrderForm.getOrderNo())) { query.setString("orderNo", "%" + searchOrderForm.getOrderNo() + "%"); } else { query.setParameter("orderNo", null); } List<OrderDetail> result = query.list(); ``` --- ## ✅ 替代方案（不推荐）：HQL + Formula 虚拟字段 你可以给 `OrderDetail` 实体加一个虚拟属性： ```java @Formula("TO_NUMBER(ORDER_ITEM_INDEX)") private Integer itemNoAsNumber; ``` 然后在 HQL 中排序： ```java order by (select o.createdDate from Order o where ...) desc, orderDetail.itemNoAsNumber asc ``` ⚠️ 缺点： - 多余字段影响性能； - 插入/更新可能异常； - 不适用于所有场景； 所以不如原生 SQL 干净利落。 --- ## ✅ 总结 | 方案 | 是否推荐 | 说明 | |------|----------|------| | ❌ HQL + `TO_NUMBER(...)` | 否 | HQL 不支持原生函数 | | ❌ HQL + 字符串排序 | 否 | `"10"` < `"2"`，错误顺序 | | ✅ Native SQL + `TO_NUMBER()` | ✅✅✅ 强烈推荐 | 简单、高效、可控 | --- ###
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值