mysql 练习

 CREATE TABLE tb_score (
 id smallint unsigned auto_increment primary key,
name varchar(20) NOT NULL,
 chinese int(11) ,
 maths int(11) 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT into tb_score (name,chinese, maths) values ('A',89,98);
 INSERT into tb_score (name,chinese, maths) values ('B',99,89);
 INSERT into tb_score (name,chinese, maths) values ('C',55,66);
 INSERT into tb_score (name,chinese, maths) values ('D',86,66);
 INSERT into tb_score (name,chinese, maths) values ('E',55,66);
 INSERT into tb_score (name,chinese, maths) values ('F',88,99);

 CREATE TABLE tb_class (
 stuname VARCHAR(10) NOT NULL,
 classname VARCHAR(10)  NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 INSERT INTO tb_class (stuname,classname) VALUES('A','C1');
 INSERT INTO tb_class (stuname,classname) VALUES('B','C2');
 INSERT INTO tb_class (stuname,classname) VALUES('C','C3');
 INSERT INTO tb_class (stuname,classname) VALUES('D','C2');
 INSERT INTO tb_class (stuname,classname) VALUES('E','C1');
 INSERT INTO tb_class (stuname,classname) VALUES('F','C3');

SELECT classname as classname ,
SUM(if (chinese>60,chinese,0)) as chinese ,
SUM(if (maths>60,maths,0)) as maths 
from tb_score  a LEFT JOIN tb_class b
on  a.name  = b.stuname 
GROUP BY (b.classname)