oracle排序问题

记录工作中遇到的问题让工作更加顺利!

1.排序

工作中遇到一个需要排序的地方，遇到两个函数 DENSE_RANK和 RANK ；RANK 函数是按照一个字段或值排序后返回绝对位置（即相同值排名相同且值不连续），DENSE_RANK 函数函数按照一个字段或值排序后返回相对位置（即排名-相同值排名相同且值连续）。查看如下图：

RANK 函数：

总结：返回的就是排序后数据的绝对位置（相同值用第一条数据的，值是间断的）。

DENSE_RANK 函数：

总结：返回排序后的相对位置（值是连续的）

用法：在查询的字段中添加 一个 DENSE_RANK() OVER (ORDER BY 字段 desc ) AS ranking 这样的字段就可以了。

注意：如果排序字段不是number 类型，是一个字符串类型但存储的是数字排序也有问题。如下

select hat.ORG_NO orgNo,hat.SCORE score, DENSE_RANK() OVER (ORDER BY hat.SCORE desc ) AS rankingfrom  TRIGGER hat

结果如下：

总结：数字的字符串，排序是按照第一个数字的大小排序的；

1.1数字字符串排序问题解决

因为字段的类型是字符串，所以需要转成数字；

TO_NUMBER函数是Oracle数据库中的一个函数，用于将字符串转换为数字。基本语法为：TO_NUMBER(str),其中，str是要转换的字符串。如果str可以被解析为数字，则返回该数字；否则，返回NULL 。

注意：因为是字符串所以肯定有为空的可能，如果为空就会返回 null。怎么解决返回NULL ？

1.2自定义处理NULL

NVL函数是Oracle数据库中的一个函数，用于处理空值(null值)。其基本语法为：NVL(expr1, expr2),其中，expr1和expr2都是表达式。如果expr1的值不为空(不为null),则将其返回；如果expr1的值为空(为null),则返回expr2的值 .

最终的sql 变成如下

select hat.ORG_NO orgNo,hat.SCORE score, DENSE_RANK() OVER (ORDER BY TO_NUMBER(nvl(hat.SCORE,0)) desc ) AS ranking from  HNNX_ANALYSIS_TRIGGER hat

反思：设计数据库时还是需要根据数据类型设计，不能偷懒直接用字符串，这样后面使用时比较费劲，甚至优化sql时无计可施。