数据库表的转换

最新推荐文章于 2024-03-15 19:29:23 发布

劈你的雷正在路上

最新推荐文章于 2024-03-15 19:29:23 发布

阅读量906

点赞数 2

分类专栏： MySQL 文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/yangchaoxu111/article/details/107347703

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

创建三张表

create table student(
    id varchar(10),
    name varchar(10)
);
desc student;

create table subject(
    subid varchar(10),
	subname varchar(10)
);
desc subject;

create table score(
    scoreid int,
	subid  varchar(10),
	stdid varchar(10),
	score  int(3)
);

desc score9;

插入数据

insert into student values ('a001','张三'),('a002','王强');

insert into subject values ('s001','英语'),('s002','数学'),('s003','语文'),('s004','历史');

insert into score values (1,'s001','a001',78),(2,'s002','a001',67),(3,'s003','a001',89),(4,'s004','a001',89),													(5,'s001','a002',89),(6,'s002','a002',67),(7,'s003','a002',84),(8,'s004','a002',96);

查询表中数据

select * from student;
select * from subject;
select * from score;

先将三张表连接成一张表结构

select student.name,subject.subname,scoreid,score 
		from student join score on student.id = score.stdid 
					 join subject on subject.subid = score.subid;

在这里插入图片描述
将横表转化为纵表

select s.name '姓名',
	sum(case s.subname when '语文' then s.score else 0 end) '语文',
	sum(case s.subname when '数学' then s.score else 0 end) '数学',
	sum(case s.subname when '英语' then s.score else 0 end) '英语',
	sum(case s.subname when '历史' then s.score else 0 end) '历史'
from (select student.name,subject.subname,scoreid,score 
		from student join score on student.id = score.stdid 
					 join subject on subject.subid = score.subid) s
group by s.name;