blog,blog,blog blog,blog,blog

blog,blog,blog

目录
我常用的SQL(Oracle)
/      

我常用的SQL(Oracle)

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 成绩相同排名相同,但不发生占位情况,

IDCOURSESCOREROW_NUMBERRANKDENSE_RANK
101数学89111
102数学89211
103数学87332
101英语92111
103英语88222
102英语81333

6.numtodsinterval,numtoyminterval

这个函数可以获取相应单位的时间,小数会被计算成整数后,再做计算:
numtodsinterval(x,c),x是一个数字,c是一个字符串,
表明x的单位,这个函数把x转为interval day to second数据类型
常用的单位有 ('day','hour','minute','second')

select sysdate,sysdate+numtodsinterval(3,'hour') as res from dual;

numtoyminterval与numtodsinterval函数类似,将x转为interval year to month数据类型

select sysdate,sysdate+numtoyminterval(3,'year') as res from dual;

常用的单位有'year','month'

未完待续...

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