SQL(Oracle)


1. coalesce 

coalesce函数可以将空值转换为实际值。

 SELECT coalesce(name,'noname') FROM user;

相比于nvl,coalesc支持多参数,按照参数排序,优先返回第一个不为空的值。

SELECT coalesce(name,nick_name,'noname') FROM user;

如果要使用nvl的话就会变成下面这样:

SELECT nvl(name,nvl(nick_name,'noname')) FROM user;

2. OREDE BY 数字 ASC

结果集排序时,除了写出具体的字段名,也可以用数字代替

SELECT name,age FROM user order by 2 ASC;
SELECT name,age FROM user order by age ASC;

上面的语句是等效的。这种写法非常适用于取值不定的时候,比如淘宝搜索结果排序,可以按价格,销量等排序

3. NULLS FIRST,NULLS LAST

在Oracle当中空值默认排序在后面,想要排到前面可以使用 NULLS FIRST,NULLS LAST。

SELECT name,age FROM user ORDER BY 2  NULLS FIRST;

4. ratio_to_report

进行报表开发时,经常需要行数据占总数百分比,使用ratio_to_report()时括号内最多只能有一个函数方法

SELECT
  sex,
  count,
  round(ratio_to_report(sum(count))
                                  OVER () * 100, 3) AS per
FROM (SELECT count(1) count,decode(sex, 1, '男', 2, '女', '未知') sex 
FROM person GROUP BY sex) GROUP BY count, sex ORDER BY per DESC

SEX COUNT PER
37215 61.128
23663 38.868
未知2 0.003

5.ROW_NUMBER、RANK、DENSE_RANK

这三个函数可以配合PARTITION BY 将主查询返回的数据分组进行分析。
以学生考试成绩为例

ID COURSE SCORE
101 数学 89
101 英语 92
102数学 89
102英语 81
103数学87
103英语88

查询出各科成绩前三名的记录

select * from (
 select id,course,score , 
row_number() OVER (PARTITION BY course ORDER BY score DESC ) AS row_number ,
rank() OVER (PARTITION BY course ORDER BY score DESC ) AS rank, 
dense_rank()OVER (PARTITION BY course ORDER BY score DESC ) AS dense_rank 
FROM exam
) WHERE rank<=3;

当使用ROW_NUMBER 排名是1,2,3即使成绩相同,排名可能不一样。
当使用RANK 排名是1,1,3即使成绩相同,成绩相同的排名相同,下一位发生了跳跃,出现占位的情况
当使用DENSE_RANK 排名 是1,1,2 成绩相同排名相同,但不发生占位情况,

ID COURSE SCORE ROW_NUMBERRANKDENSE_RANK
101 数学 89 111
102数学 89 211
103数学87332
101 英语 92 111
103英语88222
102英语 81 333

未完待续…

文章仅在黑客派和本网站刊登,其他为盗载。