sql日常积累

原创已于 2023-03-25 21:15:59 修改 · 157 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

于 2023-01-17 14:42:09 首次发布

#SQL 专栏收录该内容

1 篇文章

订阅专栏

本文展示了SQL中的一些高级操作，包括如何进行行转列，查询重复数据，实现分页查询，更新表字段，插入数据，使用LAG和LEAD函数进行偏移处理，列表agg进行分组拼接，以及NOTEXISTS子句和各种开窗函数的用法，如row_number(),rank(),dense_rank()等。

1、行转列

with temp as(
select '四川省' nation ,'成都市' 第一,'绵阳市' 第二,'德阳市' 第三,'宜宾市' 第四  from dual union all
select '湖北省' nation ,'武汉市' 第一,'宜昌市' 第二,'襄阳市' 第三,'' 第四   from dual
)
select nation,name,title from
temp
unpivot
(name for title in (第一,第二,第三,第四))t
; --unpivot（自定义列名/*列的值*/ for 自定义列名/*列名*/ in（列名））

2、查询重复数据

select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1

3、分页查询

SELECT * FROM (

　　SELECT A.*, rownum r FROM 表名 A

　　WHERE rownum <= 10 

) B WHERE r > 0

4、更新table_a表aa字段更新为table_b表bb字段

update table_a  a

/* 更新a表col字段值等于b表中coll字段的值，其中a.id=b.id */

      set  aa=(select bb from table_b b where a.id=b.id and bb is not null) 

/* 只更新a.id再b表中存在的记录 ，如果不加该条件会有问题*/

where exists (select 1 from table_b b where a.id=b.id)
;

5、insert b表数据到a表

insert into a (select * from b)

6、偏移函数使用

--lag向上偏移，lead向下偏移
select machinename, eventtime, lag(eventtime, 1, null) over(partition by machinename order by eventtime),
  lead(eventtime, 1, null) over(partition by machinename order by eventtime)  
from machinehistory mh
where eventname = 'CHGEQPSTATE'

执行结果：
![在这里插入图片描述](https://img-blog.csdnimg.cn/6d33d8bd913a476c9da735603ffcdb83.png
emmm，其实一直不太理解为啥lag是向上偏移，lead是向上偏移，明明觉得它两反了呢(如图红色箭头)？欢迎小伙伴们给我开导开导

7、分组拼接 --listagg(字段,‘拼接字符’)

select  姓名, listagg(手机号,',') within group (order by 姓名) 手机号
  from PersonInFo
  where 姓名='李四'
  group by 姓名;

8、not exists

select 列名 from 表名 where not exists (子查询);

9、开窗函数的使用

语法：函数 + over(partition by <分组用列> order by <排序用列>)

聚合函数
sum() 、count() 、min() 、max() 、 avg()
排序开窗函数
① row_number() – 相同值排名顺延，返回结果1、2、3、4
② rank() – 相同结果排名相同，后续排名不连续，返回结果为 1、2、2、4
③ dense_rank() – 相同结果排名相同，后续排名顺延，返回结果为 1、2、2、3
④ ntile(n) – 分组排名，将数据分为n组并返回对应组号1、2…n
其他函数
① lag(字段名,n,0) – 移位开窗函数，表示返回向上第n行指定字段对应数据。其中n代表向上偏移n行，0代表若偏移行数超出表范围则返回0也可以改成其他值，若不写则默认null
② lead(字段名,n,0) – 移位开窗函数，与lag()相反，表示返回向下第n行指定字段对应数据
③ first_value() – 取分组内排序后，截止到当前行，第一个值
④ last_value() – 取分组内排序后，截止到当前行，最后一个值

开窗函数不会互相干扰，在同一个查询语句中可以同时使用多个开窗函数