数据库高级语言-开窗函数-行转列-listagg

一，开窗函数：为了解决复杂的子查询引入进来的，开窗函数也是对行集组进行聚合计算的，并且它返回是多个值，目前oracle db2 sqlserver都支持，但是mysql不支持

1.row number()  over partition by :分组排名

 SELECT ORDER_NUMBER, PRODUCT_TYPE, ROW_NUMBER() OVER ( PARTITION BY ORDER_NUMBER ORDER BY PRODUCT_TYPE ) ROW_NUMBER FROM e2e_account_product_line AS k;

https://blog.csdn.net/hwt0101/article/details/79979251

https://www.cnblogs.com/lihaoyang/p/6756956.html

select * from cp_test;
--1.查找工资小于5000的员工的城市和年纪，并且每行显示员工小于5000的个数

SELECT FName,FCity,FAGE,SU from cp_test AS A CROSS JOIN (SELECT COUNT(*) AS SU from cp_test WHERE FSALARY<5000) AS B WHERE FSALARY<5000;

---使用开窗函数

SELECT FName,FCity,FAGE,count(*)over() from cp_test AS A  WHERE FSALARY<5000;
--over表示他是开窗函数，然后就会返回一组（每一行）符合条件的条数
--over(partition by FCITY) --返回所在城市的条数
SELECT FName,FCity,FAGE,count(*)over(PARTITION by FCITY) from cp_test AS A  WHERE FSALARY<5000;

 

二，行转列：pivot(行转列) 列转行(unpivot)

https://www.cnblogs.com/linJie1930906722/p/6036714.html

 

三，listagg

SELECT LISTAGG('需要拼接的列名称','拼接符号') within group  (order by '排序的列名')
from table_name

如下表:TABLE_A

id    name
1    张三
2    李四
SELECT LISTAGG(TA.NAME,';') within group (order by TA.ID DESC)
FROM TABLE_A TA

结果为：

李四;张三。

第二种：

当你的表X中有A,B两列，数据如下

A　　B

a　　1

a　　2

a　　3

b　　1

b　　2

b　　3

想让数据以 a|1|2|3 , b|1|2|3 格式显示可使用listagg()

1、使用listagg() + group by

select A,B,listagg(B,'|') within GROUP (order by A)  C from X group by A;

2、使用listagg() + over(partition by ?)

select A,B listagg(B,'|') within Group(order by A) over(partition by A)  C from X;