mysql-sql练习-5-行列互转

目录

成绩单 简单互转

需求

多行转多列 分组 判断 聚合

理解 分组 合并

逆向需求 多列转多行 输出 合并

abc 去重 合并 拆分

需求

建表

多行转多列

逆向需求 多列转多行

拆分 按长度

拆分 按个数

---

成绩单 简单互转

需求

多行转多列 分组 判断 聚合

with tmp as(-- 分组，只输出语文 其他是0 ==> 条件判断，聚合
  select
    s_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_score
  from score
  group by s_id
)
select -- 输出格式
  *,
  dense_rank() over(order by sum_score desc) dr -- 窗口范围 0-当前行
from tmp;

理解 分组 合并

select
  s_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 ==> 条件判断，聚合
    select
      s_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_score
    from score
    group 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去重
  select
    a,b,group_concat(c) c
  from abc
  group by a,b
)
select
  a,
  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(
    select
      a,b,group_concat(c) c
    from abc
    group by a,b
  )
  select
    a,
    sum(if(b = 'A',c,0)) col_A,
    max(if(b = 'B',c,0)) col_B  -- 字符串和整数聚合 只能max
  from tmp
  group 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(
    select
      a,b,group_concat(c) c
    from abc
    group by a,b
  )
  select
    a,
    sum(if(b = 'A',c,0)) col_A,
    max(if(b = 'B',c,0)) col_B  -- 字符串和整数聚合 只能max
  from tmp
  group 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;