首页 > 数据库技术 > 详细

mysql练习

时间:2019-08-22 00:34:58      阅读:98      评论:0      收藏:0      [点我收藏+]
create table STUDENT_SCORE
(
name VARCHAR(20),
subject VARCHAR(20),
score int(10)
);
insert into student_score (NAME, SUBJECT, SCORE) values (张三‘, 语文‘, 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values (张三‘, 数学‘, 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values (张三‘, 英语‘, 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values (李四‘, 语文‘, 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values (李四‘, 数学‘, 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values (李四‘, 英语‘, 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values (王五‘, 语文‘, 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values (王五‘, 数学‘, 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values (王五‘, 英语‘, 91.0);





技术分享图片

sql1、实现下列功能

技术分享图片

方法一、

select name,
 sum(case subject when 语文 then score end) as 语文,
sum(case subject when 数学 then score end) as 数学,
sum(case subject when 英语 then score end) as 英语
from student_score 
 group by name;

方法二、

select t1.name "姓名",t1.score "语文",t2.score "数学",t3.score "英语" from (select name,score from student_score where subject="语文") t1,(select name,score from student_score where subject="数学") t2,(select name,score from student_score where subject="英语") t3 where t1.name=t2.name and t1.name=t3.name;

 

mysql练习

原文:https://www.cnblogs.com/zouhong/p/11391557.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!