sql中那些偶尔用到却很实用的函数，持续更新……

最新推荐文章于 2022-12-24 14:36:36 发布

海棠xun

最新推荐文章于 2022-12-24 14:36:36 发布

阅读量527

点赞数 1

分类专栏： sql 文章标签：函数 sql

本文链接：https://blog.csdn.net/weixin_44457814/article/details/102723925

版权

sql 专栏收录该内容

16 篇文章 2 订阅

订阅专栏

1.IF() 条件判断

IF(condition, value_if_true, value_if_false)
IF函数根据条件的结果为true或false，返回第一个值，或第二个值

SELECT IF(500<1000, 5, 10);
SELECT IF(STRCMP("hello","bye") = 0, "YES", "NO");

2.MOD() 求余数

MOD(x,y) 返回 x 被 y 除后的余数，MOD() 对于带有小数部分的数值也起作用，它返回除法运算后的余数。

例：SELECT MOD(63,8),MOD(120,10),MOD(15.5,3);
结果：7   ，0，   0.5

3.DIV 整除

x div y 返回x除以y的整数，该运算符只取商的整数部分，而不会四舍五入。
div除数和被除数都可以是小数，运行也不会报错，估计是高版本的问题吧，被修复了，而且也没有四舍五入。

例：select 100.22 div 2.1;
结果：47

4. / 除以

x / y 返回x除以y的结果，最后一位四舍五入（保留几位小数不知道，知道朋友请告诉我）

例：select 100.22 / 2.1;
结果：47.723810

select trunc(1.23856789,2) from dual
round(m,n) 可以四舍五入
trunc(m,n) 直接丢弃，不四舍五入

5. sec_to_time() 秒转换为时：分：秒格式

例：select sec_to_time(610) 
结果：00:10:10
例：select sec_to_time("610")
结果： 00:10:10.000000

注意：当参数是数据库中某个字段的值的时候，如果该字段是int类型，返回的就是上例中的第一种；如果该字段是varchar类型，返回的就是上例中第二种。

6. time_to_sec() 时：分：秒格式转换为秒

例：select time_to_sec("00:10:10.000000")
结果：610
例：select time_to_sec("00:10:10")
结果： 610

7. left()、right() 和substr()

例：select left("123456",3),left(123456,3),right("123456",3),right(123456,3)
结果：123 123 456 456

从左侧（右侧）截取长度为3（length）的字符串

substr()2个参数时
例：

1.select substr("abcdef", 0)	；	//结果为空
2.select substr("abcdef", 3)；	//结果为 cdef
3.select substr("abcdef" from 3)	；	//结果为 cdef
4.select substr("abcdef", -3)；		//结果为 def

总结：
2个参数时，第二个参数标示开始位置的下标，从开始位置取到字符串结束位置为止。由1可以看出在mysql中下标从1开始计算，没有为0的下标存在；
2、3是等同的，不同的写法，同一种效果；
由4可以看出当下标为负数时，从尾部开始数；

substr()3个参数时
例：

1.select substr("abcdef", 0,3)；	//结果为空
2.select substr("abcdef", 1,3)	；	//结果为 abc
3.select substr("abcdef" from 1 for 3)	；	//结果为 abc
4.select substr("abcdef", -1,3)	；	//结果为 f
5.select substr("abcdef", 6,3)；		//结果为 f
6.select substr("abcdef", 1,-3)；		//结果为空

总结：
3个参数时，第二个参数依然是开始位置的下标，第三个参数表示取的长度。由1可以看出，下标依然是从1开始计算；
2、3是等同的，不同的写法，同一种效果；
4、5是等价的，第二个参数只表示开始的位置，-1是从后往前数1个，6是从前往后数6个，都是f，第三个参数表示的长度可以超过字符串本身的长度，不会报错，但是最多只会取到字符串最后一个位置；
由6可知，第三个参数如果是0或者负数，不会报错，但是会返回空；

8.distinct

去重，类似于group by 的效果，但是不分组，不是聚合函数。
distinct简单来说就是用来去重的，而group by的设计目的则是用来聚合统计的，两者在能够实现的功能上有些相同之处，但应该仔细区分，因为用错场景的话，效率相差可以倍计。
单纯的去重操作使用distinct，速度是快于group by的。
参考：https://blog.csdn.net/ljl890705/article/details/70602442

9.minus和except 去除交集

oracle没有except这个关键字，这个关键字属于sqlserver，在oracle里，他叫minus，与except是一个意思。MySQL没有实现minus的功能的函数。
minus”直接翻译为中文是“减”的意思，在Oracle中也是用来做减法操作的。
A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果，即结果保留，在A中存在，而在B中不存在的记录。其算法跟Java中的Collection的removeAll()类似，即A minus B将只去除A跟B的交集部分，对于B中存在而A中不存在的记录不会做任何操作，也不会抛出异常。

select * from a 
minus 
select * from b 
where name='111';

10.intersect 取交集

intersect操作符用来合并两个查询,返回两个查询中都存在的记录(返回查询结果中相同的部分),即返回两个查询结果的交集,前提是两个查询列的数量和数据类型必须完全相同。MySQL没有实现intersect 的功能的函数。

select * from a
intersect
select * from b
where id>0;

11.connect by 递归查询树形结构

递归查询当前节点的父节点或者子节点

查询所有子节点（包括当前节点）
select * from table t
start with t.id=10000
connect by prior t.id=t.parent_id
查询所有父级节点（包括当前节点）
select * from table t
start with t.id=10000
connect by prior parent_id=t.id
或者
select * from table t
start with t.id=10000
connect by t.id = prior parent_id

sys_connect_by_path(列名, 分隔符)
从start with开始的地方开始遍历，并记下其遍历到的节点，start with开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的字符串。sys_connect_by_path函数用connect by来寻找下一条记录，直到迭代找不到相应记录为止。

select *, substr(sys_connect_by_path(t.ame, '—'),2) from table t
start with t.id=10000
connect by prior t.id=t.parent_id

总结：prior关键字写在子节点前就遍历所有子节点，prior关键字放在父节点前就遍历所有父节点。

12.row_number() 排序 Oracle数据库

用途非常广泛，排序最好用它，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

select name,course,row_number() over(partition by course order by score desc) rank from student

按照course课程分组，在组内按照score成绩进行排序。
参考：https://www.cnblogs.com/qiuting/p/7880500.html

13.with # as（）相当于一个子查询

WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。

with a as (select * from table)
select * from a

/*+MATERIALIZE */ 或者 --+materilize 相当于把子查询里面的结果存成临时表放内存里（有效优化sql执行速度）

with a as (select  /*+MATERIALIZE */ * from table)
select * from a

14.regexp_like（）相当于str.matches()

regexp_like(字段，正则表达式，match_type参数)
match_type参数可以包含以下字符:

c：区分大小写的匹配。

i：不分大小写匹配。

m：多重线模式。识别字符串中的行终止符。默认行为是仅在字符串表达式的开始和结束处匹配行终止符。

n：该.字符与行终止符匹配。默认设置是.匹配以在行尾停止。

u：仅限Unix的行结尾。只有换行符被.、^和$ 匹配运算符识别为行结束符。

select * from dual where regexp_like('Cat', '^ca', 'c');
select * from dual where regexp_like('Cat', '^ca', 'i');

15.length()获取长度

VARCHAR2、NUMBER、 CLOB等等任意类型都可以用这个函数

select t.*, length(t.name) from table t order by length(t.name) desc;

16. over()

-- Create table
create table EMPLOYEE
(
  ID         NUMBER,
  NAME       VARCHAR2(10),
  SALARY     NUMBER,
  DEPARTMENT VARCHAR2(10)
)
tablespace TBS_PIER_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited
  );
insert into pier.employee (ID, NAME, SALARY, DEPARTMENT)
values (1, '张三', 30, '1');

insert into pier.employee (ID, NAME, SALARY, DEPARTMENT)
values (2, '李四', 20, '1');

insert into pier.employee (ID, NAME, SALARY, DEPARTMENT)
values (3, '王五', 10, '1');

insert into pier.employee (ID, NAME, SALARY, DEPARTMENT)
values (4, '马六', 50, '2');

insert into pier.employee (ID, NAME, SALARY, DEPARTMENT)
values (5, '冯七', 40, '2');

insert into pier.employee (ID, NAME, SALARY, DEPARTMENT)
values (6, '朱八', 45, '2');

在这里插入图片描述

与row_number()、rank()、dense_rank() 联用，取组内第一名
三者具体区别参考：https://www.cnblogs.com/qiuting/p/7880500.html

select *
  from (select t.*,
               rank() over(partition by t.department order by t.salary desc) as rank
          from employee t) a
 where a.rank = 1;

结果：
在这里插入图片描述
与其他聚合函数连用

select t.*,
       sum(t.salary) over() as a1,
       count(*) over() as a2,
       max(t.id) over() as a3,
       sum(t.salary) over(order by t.id) as b1,
       count(*) over(order by t.id) as b2,
       max(t.id) over(order by t.salary desc) as b3,
       sum(t.salary) over(partition by t.department) as c1,
       count(*) over(partition by t.department) as c2,
       max(t.id) over(partition by t.department) as c3,
       sum(t.salary) over(partition by t.department order by t.id desc) as d1,
       count(*) over(partition by t.department order by t.id desc) as d2,
       max(t.id) over(partition by t.department order by t.salary desc) as d3,
       rank() over(partition by t.department order by t.salary desc) as rank1,
       rank() over(order by t.salary desc) as rank2
  from employee t

结果：
在这里插入图片描述

select t.*,
       first_value(t.salary) over(partition by t.department order by t.id) as a1,
       first_value(t.salary) over(partition by t.department) as a2,
       last_value(t.salary) over(partition by t.department order by t.id) as b1,
       last_value(t.salary) over(partition by t.department) as b2,
       last_value(t.salary) over(partition by t.department order by t.id desc
        rows between unbounded preceding and unbounded following) as b3,
       lag(t.salary) over(partition by t.department order by t.id) as c1,
       lag(t.salary, 1) over(partition by t.department order by t.id) as c1,
       lag(t.salary, 1, 0) over(partition by t.department order by t.id) as c2,
       lead(t.salary) over(partition by t.department order by t.id) as d1,
       lead(t.salary, 1) over(partition by t.department order by t.id) as d1,
       lead(t.salary, 1, 0) over(partition by t.department order by t.id) as d2
  from employee t

结果：
在这里插入图片描述
a1和b3完全一样，可以酌情使用其中一种。
b1: last_value(order by …)默认统计范围是 rows between unbounded preceding and current row，也就是取当前行数据与当前行之前的数据的比较。
b3: rows between unbounded preceding and unbounded following，表示“无界前行和无界后行之间的行”，就可以取到整个组中排序后最后一行的值了。

lag(field, num, defaultvalue) field需要查找的字段，num往前查找的num行的数据，defaultvalue没有符合条件的默认值。
lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。

总结：
每个over对应的结果，都是该over内的条件生成的分组和排序后的值，多个over之间不会相互影响；
partition by 就是分组，相当于group by。没有partition by就相当于没有group by而直接使用聚合函数，即整个结果集为一组；
order by是组内排序的意思。没有order by相当于没有排序，在组内直接聚合。有order by是一个“连续”聚合的结果，及按order by 的第一条数据的over结果，就是这条数据的聚合、按order by的第二条数据的over结果，就是第一条和第二条数据的聚合。

17.wmsys.wm_concat()

wmsys.wm_concat是一个聚合函数，其作用是将一列数据转换成一行，也就是我们常用的行专列，但是该函数是一个undocument函数，所以不推荐使用这个函数。因为在后续的版本中还提不提供这个函数都不好说呢。
wmsys.wm_concat是一个类似sum、max等的聚合函数，用于字符串的连接，因此一般要搭配group by 或 over(partition xx)使用。默认用‘，’连接多个字符串
例如现在有个学生表，表里有学生名字，学生报名学科两个字段

create table student(
name varchar2(20),
subject varchar2(30)
);
 
insert into student values('张三','英语');
insert into student values('张三','数学');
insert into student values('张三','语文');
insert into student values('李四','物理');
insert into student values('李四','化学');

现在有个业务场景，我查询每个学生报名学科，但是每个学生只要一条数据展示，也就是将学科整合起来，以便于查看。

可以用如下查询语句进行查询

select name,wmsys.wm_concat(subject) as subject
from student
group by name

当然也可以这样何使用

select wmsys.wm_concat(subject) as subject from student
相当于直接使用聚合函数，整个结果为一组 类似于
select count(*) from student
select max(name) from student

使用该函数有以下问题：

1.该函数不是oracle公开的系统函数，它的用户是wmsys,而不是sys或者system，oracle很有可能在版本升级或者补丁的时候取消或者修改这个函数甚至用户，这种变化oracle是不会公开的。所有可能会由于这个变化而导致异常。

2.大量使用这个函数也会导致临时表空间爆满，这是因为在10.2.0.5中，使用wmsys.wm_concat返回的结果格式是CLOB，CLOB占用的临时表空间只有在连接释放后才会释放，部分通过连接池连接数据库的长连接很有可能导致CLOB占用临时表空间不断累积增大，会导致临时表空间爆满的故障

3.如果是在程序中大量使用这个函数的话会引起enq:TT的锁，可能会导致某些对象被锁。

另外在项目中发现在mybatis中如果将wmsys.wm_concat结果保存在一个类对象中没问题，但是如果存在一个hashmap中结果就是weblogic的clob对象的toString()结果，也就是getClass().getName() + ‘@’ + Integer.toHexString(hashCode())，解决方法就是在wmsys.wm_concat函数外套一个to_char函数就没问题了

18.SEQ.nextval还可以这样用

SELECT SEQ.nextval, t.* FROM
employee t;

在这里插入图片描述
为每一行都生成一个序列号，在批量导入的时候可以结合

insert into table (column1, column2, column3,
column4,column5, column6
)
SELECT SEQ_table_ID.nextval, A.* FROM
<foreach collection="records" item="record" index="index" close=")" open="(" separator="union all">
  SELECT
  #{record.customerId} AS column1,
  #{record.userId} AS column2,
  #{record.checkStatus} AS column3,
  #{record.createBy} AS column4,
  #{record.createDate} AS column5,
  #{record.updateBy} AS column6
  from dual
</foreach> A

标签使用

海棠xun

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
sql中那些偶尔用到却很实用的函数，持续更新……

1.IF()IF(condition, value_if_true, value_if_false)IF函数根据条件的结果为true或false，返回第一个值，或第二个值例：SELECT IF(500<1000, 5, 10);SELECT IF(STRCMP(“hello”,“bye”) = 0, “YES”, “NO”);...
复制链接

扫一扫