侧边栏壁纸
  • 累计撰写 138 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

【ORACLE】收集一些较为少见但很有用的SQL函数及写法.part1

DarkAthena
2021-09-19 / 0 评论 / 1 点赞 / 1098 阅读 / 8581 字

前言

sql作为传统关系型数据一种常见语言,广泛使用在各种程序项目中。随着数据库厂商不断地更新迭代版本,sql的功能越来越丰富和强大,已经不局限于关系型查询,甚至对递归、数组、对象等都有支持。但是实际开发中,大多数开发人员对sql研究并不深,不清楚sql能支持到何种程度。虽然近几年网络上已经开始有一些技术分析文了,但总感觉受众还是不够广,所以我就开一个专题系列,分享一些sql的写法,主要针对oracle数据库,每篇根据复杂度大概介绍不超过10种,下面开始第一篇,先来点简单的


1.cast

作用:强制或者指定查询字段的输出类型,常用于“CREATE TABLE AS SELECT ...”中
例:

create table  tmp_table as 
select CAST(132 as number(8, 4)) number_col,
       CAST(132 as VARCHAR2(10)) VARCHAR2_col,
       cast(null as date) date_null,
       cast(null as number) number_null,
       cast(null as VARCHAR2(10)) varchar2_null,
       '1' char_null
  from dual;
desc tmp_table;
名称是否为空?类型
NUMBER_COL NUMBER(8,4)
VARCHAR2_COL VARCHAR2(10)
DATE_NULL DATE
NUMBER_NULL NUMBER
VARCHAR2_NULL VARCHAR2(10)
CHAR_NULL CHAR(1)

注意观察char_null这个字段和varchar2_null字段的区别,varchar2是可变长字符串,在存储空间的节省上,要优于char,建议尽量避免使用char类型。

而且,如果使用下面这2种写法,是会报错的,不允许长度为0的列

create table  tmp_table as 
select 
       '' char_null
  from dual;
create table  tmp_table as 
select 
       null char_null
  from dual;

2.with ..as

作用:将子查询提取出来作为一个虚拟表,简化sql,增强sql的可读性,有时也能在一定程度上提升sql的查询效率(这个其实还有更复杂的用法,以后再说)
例:

with t as
 (select null expr1, 2 expr2, 3 expr3 from dual)
select t1.expr2 + t2.expr3 res from t t1, t t2

此例子中,对同一个子查询sql引用了两次,常规写法要重复写两个一模一样的,但使用with子句可以精简为只写一个子查询sql,而且with子句后还支持并列或者嵌套多个sql,比如

with t as
 (select null expr1, 2 expr2, 3 expr3 from dual),
     t1 as
 (select * from t where t.expr2=2)
select * from t ,t1

3.NVL2

作用:该函数有3个参数,判断第一个参数是否为空,如果不为空则显示第二个参数,如果为空则显示第三个参数
例:

with t as
 (select null expr1, 2 expr2, 3 expr3 from dual)
select NVL2(expr1, expr2, expr3) res from t;
RES
3

注意区分该函数和nvl的区别


4.COALESCE

作用:该函数是nvl函数的升级版,可传入很多个参数,按参数输入顺序,输出第一个不为空的参数的值
例:

with t as
 (select null expr1, null expr2, 3 expr3 from dual)
select COALESCE(expr1, expr2, expr3) res from t;

RES
3

5.TRANSLATE

作用:对第一个参数中的字符串进行替换,替换规则为,把第二个参数中的第N个字符替换成第三个参数中的第N个字符。可以用于字符串加密
例:

select  TRANSLATE('A爱B,但是B不爱A','A爱','C恨') res   from dual;
RES
C恨B,但是B不恨C

6.RPAD

作用:在第一个参数右边以第三个参数填充,使输出结果长度达到第二个参数的值,类似的还有LPAD,即在左边填充。常用于输出一些格式化文本的处理
例:

select RPAD('我银行卡上的数字为100',50,'0') res  from dual;
RES
我银行卡上的数字为10000000000000000000000000000000

7.SIGN

作用:判断输入参数的正负号,正数返回1,负数返回-1,0返回0。在做数据统计时,如果求和的列有正有负,要分别求出正数的和和负数的和,或者说正数和负数的数据要分开处理,那么在group by 后面加上sign(求和列)则是个不错的方式。
例:

with t as 
(select 'A' NAME,-2 VALUE FROM DUAL UNION ALL
select 'B',3 from dual union all
select 'A',-1 from dual union all
select 'A',5 from dual union all
select 'B',2 from dual 
)

SELECT NAME ,SUM(VALUE) S FROM T GROUP BY NAME,SIGN(VALUE)
NAMES
B5
A-3
A5

我们可以猜想A和B两个人,后面的统计是拉到的客户数,正数表示成功人数,负数表示失败人数,虽然最后成功的人数一样,但B属于稳扎稳打,没有失败,但A则失败了3个,这个可能会造成一些负面影响。当然也可以用其他角度来观察,这就靠人自行想象了。


8.ROLLUP/CUBE

作用:接在group by 后面,查询结果能多出几行不同的聚合维度的数据。其中ROLLUP是按后面接的参数顺序逐级聚合,CUBE则是列出后面参数所有组合情况的聚合。
例:

WITH T AS 
(SELECT 'A' NAME,-2 VALUE,3 WEIGHT FROM DUAL UNION ALL
SELECT 'B',3 , 3 FROM DUAL UNION ALL
SELECT 'A',-1,2 FROM DUAL UNION ALL
SELECT 'A',5,2 FROM DUAL UNION ALL
SELECT 'B',2,4 FROM DUAL 
)

SELECT NAME ,WEIGHT,SUM(VALUE) S FROM T GROUP BY ROLLUP (NAME,WEIGHT)
NAMEWEIGHTS
A24
A3-2
A 2
B33
B42
B 5
7

这个例子中,输出结果的第三行,表示 NAME为A的汇总行,不管WEIGHT;第6行为B的汇总行;第7行为所有的汇总。在做一些报表数据时,不需要再另外针对不同的汇总维度再多写几段sql了,而且性能开销更低。结合NVL函数,可以让数据结果更直观,比如

WITH T AS 
(SELECT 'A' NAME,-2 VALUE,3 WEIGHT FROM DUAL UNION ALL
SELECT 'B',3 , 3 FROM DUAL UNION ALL
SELECT 'A',-1,2 FROM DUAL UNION ALL
SELECT 'A',5,2 FROM DUAL UNION ALL
SELECT 'B',2,4 FROM DUAL 
)

SELECT nvl(NAME, '全员') NAME,
       nvl(to_char(WEIGHT), '合计') WEIGHT,
       SUM(VALUE) S
  FROM T
 GROUP BY ROLLUP(NAME, WEIGHT)
NAMEWEIGHTS
A24
A3-2
A合计2
B33
B42
B合计5
全员合计7

9.LISTAGG(...,',') within GROUP (order by ...) /(WM_CONCAT)

作用:将某列数据的值放到一个值里,用指定的分隔符串起来,且支持排序(分隔符可自定义,上面是指定逗号),在某种意义上可以理解为是行转列的一种方式。
例:

with t as 
(select 'A' NAME,-2 VALUE FROM DUAL UNION ALL
select 'B',3 from dual union all
select 'A',-1 from dual union all
select 'A',5 from dual union all
select 'B',2 from dual 
)
SELECT LISTAGG(NAME,',')  within GROUP (order by NAME) res FROM t 
RES
A,A,A,B,B

可以看出这里的结果并没有去重,这里就要提到另一个函数了WM_CONCAT,WM_CONCAT支持WM_CONCAT(DISTINCT
字段) 的写法,
但是,
一般在oralce 11g版本及之前,可以用wm_concat这个函数,使用起来更简单,但是从12C版本开始,ORACLE移除了这个函数,因为WM_CONCAT这个函数一开始并不是开放给用户使用的,所以在正式的代码里尽量使用LISTAGG而非WM_CONCAT,以免程序移植或者数据库版本升级导致程序出现异常。当然如果一定要用wm_concat,也可以自行在12C以上的版本把这个wm_concat函数给加上去,这里就不给代码了,请自行在互联网检索。
不过,ORACLE既让我们改用LISTAGG又不给去重,这合理么?
不合理。
所以,ORACLE 在19C版本把这个功能加上去了,而这个功能,在众多流行的数据库中,在这个时候只有DB2和ORACLE这2款数据库支持LISTAGG里加distinct(未来可能会有更多的数据库支持)。
而且,从oracle 18c版本开始,LISTAGG可以不需要 “within GROUP ”了,效果如下

with t as 
(select 'A' NAME,-2 VALUE FROM DUAL UNION ALL
select 'B',3 from dual union all
select 'A',-1 from dual union all
select 'A',5 from dual union all
select 'B',2 from dual 
)
SELECT LISTAGG(NAME)  res,LISTAGG(NAME,',') res2  FROM t 
RESRES2
ABAABA,B,A,A,B

另外一个要注意的点是,WM_CONCAT输出结果是4000长度以内的CLOB类型,LISTAGG是4000长度以内的varchar2类型,所以 别拼太长!

1
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin
博主关闭了所有页面的评论