Oracle的行转列

2019独角兽企业重金招聘Python工程师标准>>>

一，简单的行转列

做项目的时候碰到一个需求，老板有三个门店，每个门店每天都有金额入账，现在想展示某一段时间每个门店的收入情况

原表的的结构

这里在sql里面处理就比较方便，只需用到sql的decode()，和sum()函数就可以实现

select d.md,
sum(DECODE(rq, '星期一', je, null)) as monday,
sum(DECODE(rq, '星期二', je, null)) as tuesday,
sum(DECODE(rq, '星期三', je, null)) as wednesday,
sum(DECODE(rq, '星期四', je, null)) as thursday,
sum(DECODE(rq, '星期五', je, null)) as friday,
sum(DECODE(rq, '星期六', je, null)) as saturday,
sum(DECODE(rq, '星期天', je, null)) as Sunday
from TESTHZL d 
GROUP BY d.md 

效果

一小点扩展，如果需要统计这段时间的总收入

select t.*,t.monday+t.tuesday+t.wednesday+t.thursday+t.friday+t.saturday+t.Sunday count from (
select d.md,
sum(DECODE(rq, '星期一', je, null)) as monday,
sum(DECODE(rq, '星期二', je, null)) as tuesday,
sum(DECODE(rq, '星期三', je, null)) as wednesday,
sum(DECODE(rq, '星期四', je, null)) as thursday,
sum(DECODE(rq, '星期五', je, null)) as friday,
sum(DECODE(rq, '星期六', je, null)) as saturday,
sum(DECODE(rq, '星期天', je, null)) as Sunday
from TESTHZL d 
GROUP BY d.md)t 

很简单就实现了

二，思考与扩展

现在假如我们有一个需求，在某一年级，有三个班级，每个班级取总分前三的尖子生，比较每个班级的情况（与行转列无关的就不涉及了，例如怎么取班级分数前三的尖子生）

原始表字段结构

数据格式

SELECT class,
max(decode(r,1,student,null)) student1,
max(decode(r,1,count,null)) count1,
max(decode(r,1,chinese,null)) chinese1,
max(decode(r,2,student,null)) student2,
max(decode(r,1,count,null)) count2,
max(decode(r,1,chinese,null)) chinese2,
max(decode(r,3,student,null)) student3,
max(decode(r,1,count,null)) count3,
max(decode(r,1,chinese,null)) chinese3
from (
select t.*,row_number() 
over(partition by class order by student ) r 
from CLASS_TOPSTUDENT t
)
group by class

 主要是 max()   over   (PARTITION   BY   ...)   这个分析函数的应用

 

转载于:https://my.oschina.net/ThreeTiger/blog/1574654