mysql> show create table SC \G
*************************** 1. row ***************************
Table: SC
Create Table: CREATE TABLE `SC` (
`Sid` varchar(10) DEFAULT NULL,
`Cid` varchar(10) DEFAULT NULL,
`score` decimal(18,1) DEFAULT NULL,
KEY `Sid` (`Sid`),
KEY `Cid` (`Cid`),
CONSTRAINT `SC_ibfk_1` FOREIGN KEY (`Sid`) REFERENCES `Student` (`Sid`),
CONSTRAINT `SC_ibfk_2` FOREIGN KEY (`Cid`) REFERENCES `Course` (`Cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.05 sec)
mysql> desc SC
-> ;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| Sid | varchar(10) | YES | MUL | NULL | |
| Cid | varchar(10) | YES | MUL | NULL | |
| score | decimal(18,1) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.48 sec)
mysql> select * from SC;
+------+------+-------+
| Sid | Cid | score |
+------+------+-------+
| 01 | 01 | 80.0 |
| 01 | 02 | 90.0 |
| 01 | 03 | 99.0 |
| 02 | 01 | 70.0 |
| 02 | 02 | 60.0 |
| 02 | 03 | 80.0 |
| 03 | 01 | 80.0 |
| 03 | 02 | 80.0 |
| 03 | 03 | 80.0 |
| 04 | 01 | 50.0 |
| 04 | 02 | 30.0 |
| 04 | 03 | 20.0 |
| 05 | 01 | 76.0 |
| 05 | 02 | 87.0 |
| 06 | 01 | 31.0 |
| 06 | 03 | 34.0 |
| 07 | 02 | 89.0 |
| 07 | 03 | 98.0 |
+------+------+-------+
18 rows in set (0.00 sec)
以上是我的Scoure表数据
统计每个学生的总成绩
select sid,sum(score) from SC group by sid;
统计每个学生不及格和及格科目数
select sid,sum(score <60) ，sum(score >= 60) from SC group by sid;
统计不及格科目大于2的学生的平均成绩
select sid,avg(score) from SC where score < 60 group by sid having count(*) >2;
统计每一科的及格和不及格人数及及格率
select cid,sum(score < 60) 不及格,sum(score >= 60) 及格,sum(case when score >= 60 then 1 else 0 end )/count(distinct sid) 及格率 from SC group by cid;
本文转自 aklaus 51CTO博客，原文链接:http://blog.51cto.com/aklaus/1597507
