sql 行列转换

最新推荐文章于 2021-06-25 02:04:41 发布

原创最新推荐文章于 2021-06-25 02:04:41 发布 · 127 阅读

0 ·

CC 4.0 BY-SA版权

mysql 同时被 2 个专栏收录

5 篇文章

订阅专栏

oracle

2 篇文章

订阅专栏

本文详细介绍了如何使用SQL语句创建成绩表、学生表和科目表，并通过综合查询展示了如何计算学生的总成绩。利用CASE语句进行条件查询，最后展示查询结果，提供了一个关于学生学术表现的全面视图。

建表语句


/*Table structure for table `score` */

drop table if exists `score`;

CREATE TABLE `score` (
  `id` int(10) NOT NULL auto_increment,
  `score` int(3) default NULL,
  `subject` int(10) default NULL,
  `student` int(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `score` */

insert into `score` values (1,100,1,1),(2,99,2,1),(3,99,1,2),(4,98,2,2),(5,99,3,2);

/*Table structure for table `student` */

drop table if exists `student`;

CREATE TABLE `student` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `student` */

insert into `student` values (1,'zhangsan'),(2,'lisi');

/*Table structure for table `subject` */

drop table if exists `subject`;

CREATE TABLE `subject` (
  `id` int(10) NOT NULL auto_increment,
  `subject` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `subject` */

insert into `subject` values (1,'chinese'),(2,'math'),(3,'english');

查询语句


select t.* ,(t.chinese + t.math+ t.english) as total from (select stu.name,max(case  sub.subject when  'chinese' then s.score else 0 end) as chinese,max(case  sub.subject when  'math' then s.score else 0 end) as math,max(case  sub.subject when  'english' then s.score else 0 end) as english  from student stu,score s,subject sub where stu.id = s.student and sub.id = s.subject group by name) t

结果：
[img]http://dl.iteye.com/upload/attachment/0067/8315/b68712ca-a75f-371e-8bb0-a46829f5d94d.png[/img]