最近有个需求需要统计用户在我们小程序上各个功能模块的点击量,管理后台列表中需要以用户名、各个模块名作为列名展示,这时组长提了一下,可能需要用到数据库表行转列,然后在网上学习了一下,写了一个小案例如下:
这是一张记录学生每个科目成绩的表:
统计需求:查询出每个学生各个学科的成绩
sql语句:
select * from "ZXX_TEST_CHENG_ji" PIVOT (MAX(FEN_SHU) FOR KE_MU IN ('语文' as "YUWEN",'数学' as "SHUXUE",'英语' as "YINYU"));
查询结果:
sql分析:
PIVOT:行转列操作符
KE_MU:需要将数据转成列的字段名
MAX(FEN_SHU):指定要聚合的字段名,及聚合方式(为啥一定要使用聚合函数,是为了保证转成列的值唯一,不影响统计的主记录数,如上,李四英语有两个成绩,max聚合后为80,min聚合后为75)
IN ('语文' as "YUWEN",'数学' as "SHUXUE",'英语' as "YINYU")):将要作为列的数据进行指定(如:语文),并取一个别名(如YUWEN)
执行原理:
先以未参与行转列的字段进行分组(可能是一个也可能是多个)得出主记录且列名不变,然后对组内数据按照指定规则进行列名生成及列值聚合,最后返回查询结果