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;