目录
成绩单 简单互转
需求
多行转多列 分组 判断 聚合
理解 分组 合并
逆向需求 多列转多行 输出 合并
abc 去重 合并 拆分
需求
建表
多行转多列
逆向需求 多列转多行
拆分 按长度
拆分 按个数
成绩单 简单互转
需求
多行转多列 分组 判断 聚合
with tmp as(-- 分组,只输出语文 其他是0 ==> 条件判断,聚合selects_id,sum(if(c_id = '01',score,0)) '语文01',sum(if(c_id = '02',score,0)) '数学02',max(if(c_id = '03',score,0)) '英语03',sum(score) sum_scorefrom scoregroup by s_id
)
select -- 输出格式*,dense_rank() over(order by sum_score desc) dr -- 窗口范围 0-当前行
from tmp;
理解 分组 合并
selects_id, -- 分组:右侧括号图 多行group_concat(c_id) c_id, -- 多行合并成一行字符串 有空则空group_concat(score) score,group_concat(if(c_id = '01',score,0)) '语文01',group_concat(if(c_id = '01',score,0)) '数学02',group_concat(if(c_id = '01',score,0)) '英语03'
from score
group by s_id;
逆向需求 多列转多行 输出 合并
with tmp as(with tmp as(-- 分组:右侧括号图,只输出语文 其他是0 ==> 条件判断,聚合selects_id,sum(if(c_id = '01',score,0)) '语文01',sum(if(c_id = '02',score,0)) '数学02',max(if(c_id = '03',score,0)) '英语03',sum(score) sum_scorefrom scoregroup by s_id)select -- 输出格式*,dense_rank() over(order by sum_score desc) dr -- 窗口范围 0-当前行from tmp
)
select s_id,'01' c_id,语文01 score from tmp union -- 输出需要的列 合并
select s_id,'02' c_id,数学02 score from tmp union
select s_id,'03' c_id,英语03 score from tmp;
abc 去重 合并 拆分
需求
建表
create table abc(a int comment '年份',b varchar(2) comment '字母',c int comment '整数'
) comment '行列互转 合并拆分';insert into abc
values('2014','A',10),('2014','B',9),('2014','B',6),('2015','A',8),('2015','B',7);
select * from abc;
多行转多列
with tmp as(-- b去重selecta,b,group_concat(c) cfrom abcgroup by a,b
)
selecta,sum(if(b = 'A',c,0)) col_A,max(if(b = 'B',c,0)) col_B -- 字符串和整数聚合 只能max
from tmp
group by a;
逆向需求 多列转多行
拆分 按长度
with tmp as(-- 作为初始表with tmp as(selecta,b,group_concat(c) cfrom abcgroup by a,b)selecta,sum(if(b = 'A',c,0)) col_A,max(if(b = 'B',c,0)) col_B -- 字符串和整数聚合 只能maxfrom tmpgroup by a
)
select a,'A' b,col_A c from tmp union -- 输出需要的列 重命名
select a,'B' b,substring(col_B,1,1) c from tmp union -- 去重
select a,'B' b,substring(col_B,-1,1) c from tmp
order by a;
拆分 按个数
with tmp as(-- 作为初始表with tmp as(selecta,b,group_concat(c) cfrom abcgroup by a,b)selecta,sum(if(b = 'A',c,0)) col_A,max(if(b = 'B',c,0)) col_B -- 字符串和整数聚合 只能maxfrom tmpgroup by a
)
select a,'A' b,col_A c from tmp union
select a,'B' b,substring_index(col_B,',',1) c from tmp union
select a,'B' b,substring_index(col_B,',',-1) c from tmp
order by a;