SQL查询-行列转换

问题一

创建表格

create table course(
id int,
teacher_id int,
week_day int,
has_course varchar(10))

插入数据

insert into course value
(1,1,2,"Yes"),
(2,1,3,"Yes"),
(3,2,1,"Yes"),
(4,3,2,"Yes"),
(5,1,2,"Yes");

查看数据

select * from course

在这里插入图片描述
实现行列转变:

select teacher_id,
(case when week_day=1 then has_course else null end) as Mon,
(case when week_day=2 then has_course else null end) as Tue,
(case when week_day=3 then has_course else null end) as Wen,
(case when week_day=4 then has_course else null end) as Thu,
(case when week_day=5 then has_course else null end) as Fri
from course

在这里插入图片描述
若不显示null,可使用如下代码:

select teacher_id,
(case when week_day=1 then has_course else ''end) as mon,
(case when week_day=2 then has_course else ''  end) as tue,
(case when week_day=3 then has_course else ''  end) as wen,
(case when week_day=4 then has_course else  '' end) as thu,
(case when week_day=5 then has_course else  '' end) as fri
from course

在这里插入图片描述

问题二

创建表格

create table a1 (
name varchar(20),
english int,
maths int,
music int);

插入数据

insert into a1 values
("Jim",90,88,99);

查看数据

select * from a1

在这里插入图片描述

实现数据逆透视

select name,'english' as subject,english from a1
union all
select name,'maths' as subject,maths from a1
union all
select name,'music' as subject,music from a1

在这里插入图片描述

问题三

有数据表如下:

select * from row_col_table rct

在这里插入图片描述
对数据实现数据透视

select year_num,
sum(case when month_num=1 then sales else 0 end) as m1,
sum(case when month_num=2 then sales else 0 end) as m2,
sum(case when month_num=3 then sales else 0 end) as m3,
sum(case when month_num=4 then sales else 0 end) as m4 
from row_col_table
group by year_num

在这里插入图片描述

数据透视+汇总

select year_num,
max(case when month_num=1 then sales else 0 end) as m1,
max(case when month_num=2 then sales else 0 end) as m2,
max(case when month_num=3 then sales else 0 end) as m3,
max(case when month_num=4 then sales else 0 end) as m4,
sum(sales) as sales
from row_col_table
group by year_num

在这里插入图片描述

  • 0
    点赞
  • 2
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:1024 设计师:我叫白小胖 返回首页
评论

打赏作者

Martin Le

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值