MySQL 子查询和分组查询

drop database if exists `Helenlyn_Class`;
create database `Helenlyn_Class`;

/*班级表*/
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
 `classid` int primary key AUTO_INCREMENT comment '班级id',
 `classname` varchar(30) DEFAULT NULL comment '班级名称'
) ENGINE=InnoDB comment '班级表';

insert into `classes`(`classname`)
values ('初三一班'),('初三二班'),('初三三班');

/*学生表：这边假设学生id和姓名都具有唯一性*/

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
 `studentid` int primary key NOT NULL AUTO_INCREMENT comment '学生id',
 `studentname` varchar(20) DEFAULT NULL comment '学生姓名',
 `score` DECIMAL(10,2) DEFAULT NULL comment '毕业成绩',
 `classid` int(4) DEFAULT NULL comment '所属班级id，来源于classes表的classid'
) ENGINE=InnoDB comment '学生表';
insert into `students`(`studentname`,`score`,`classid`) values
('brand',97.5,1),('helen',96.5,1),('lyn',96,1),('sol',97,1),('weng',100,1),('diny',92.7,1),
('b1',81,2),('b2',82,2),('b3',83,2),('b4',84,2),('b5',85,2),('b6',86,2),
('c1',71,3),('c2',72.5,3),('c3',73,3),('c4',74,3),('c5',75,3),('c6',76,3);


/*毕业考核分数排名表*/
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores`(
 `scoregrad` varchar(3) primary key comment '等级：S、A、B、C、D',
 `downset` int comment '分数评级下限',
 `upset` int comment '分数评级上限'
) comment '毕业考核分数排名表';
INSERT INTO `scores` values ('S', 91, 100),('A', 81, 90),('B', 71, 80),('C', 61, 70),('D', 51,60);

mysql> select a.classid as 班级编号,a.classname as 班级名称,
(select count(*) from students b where b.classid = a.classid) as 学生数量
from classes a;
+----------+----------+----------+
| 班级编号 | 班级名称 | 学生数量 |
+----------+----------+----------+
|    1 | 初三一班 |    6 |
|    2 | 初三二班 |    6 |
|    3 | 初三三班 |    6 |
+----------+----------+----------+
3 rows in set

mysql> select
(select classname from classes a,students b where a.classid = b.classid and b.studentname='brand')
as 班级;
+----------+
| 班级   |
+----------+
| 初三一班 |
+----------+
1 row in set

mysql> select a.classid,avg(a.score) from students a group by a.classid;

+---------+--------------+
| classid | avg(a.score) |
+---------+--------------+
|    1 | 96.616667  |
|    2 | 83.500000  |
|    3 | 73.583333  |
+---------+--------------+
3 rows in set

mysql> select * from scores order by upset desc;

+-----------+---------+-------+
| scoregrad | downset | upset |
+-----------+---------+-------+
| S     |   91 |  100 |
| A     |   81 |  90 |
| B     |   71 |  80 |
| C     |   61 |  70 |
| D     |   51 |  60 |
+-----------+---------+-------+
5 rows in set

select a.classid as 班级id,a.avgscore 平均毕业分数,b.scoregrad 分数评级 from
(select classid,avg(score) as avgscore from students group by classid) as a,
scores b where a.avgscore between b.downset and b.upset;

+--------+--------------+----------+
| 班级id | 平均毕业分数 | 分数评级 |
+--------+--------------+----------+
|   1 | 96.616667  | S    |
|   2 | 83.500000  | A    |
|   3 | 73.583333  | B    |
+--------+--------------+----------+
3 rows in set

mysql> select * from students a where a.score >(select b.score from students b where b.studentname='diny');
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
|     1 | brand    | 97.5 |    1 |
|     2 | helen    | 96.5 |    1 |
|     3 | lyn     | 96  |    1 |
|     4 | sol     | 97  |    1 |
|     5 | weng    | 100  |    1 |
+-----------+-------------+-------+---------+
5 rows in set

mysql> select * from students a where
a.score <(select b.score from students b where b.studentname='diny')
and a.classid <> (select b.classid from students b where b.studentname='diny') ;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
|     7 | b1     | 81  |    2 |
|     8 | b2     | 82  |    2 |
|     9 | b3     | 83  |    2 |
|    10 | b4     | 84  |    2 |
|    11 | b5     | 85  |    2 |
|    12 | b6     | 86  |    2 |
|    13 | c1     | 71  |    3 |
|    14 | c2     | 72.5 |    3 |
|    15 | c3     | 73  |    3 |
|    16 | c4     | 74  |    3 |
|    17 | c5     | 75  |    3 |
|    18 | c6     | 76  |    3 |
+-----------+-------------+-------+---------+
12 rows in set

mysql> select a.classid,avg(a.score) as avgscore from students a group by a.classid
having avgscore < (select avg(score) from students);
+---------+-----------+
| classid | avgscore |
+---------+-----------+
|    2 | 83.500000 |
|    3 | 73.583333 |
+---------+-----------+
2 rows in set

mysql> select * from students a where a.classid in (select distinct b.classid from classes b where b.classid <3);
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
|     1 | brand    | 97.5 |    1 |
|     2 | helen    | 96.5 |    1 |
|     3 | lyn     | 96  |    1 |
|     4 | sol     | 97  |    1 |
|     5 | weng    | 100  |    1 |
|     6 | diny    | 92.7 |    1 |
|     7 | b1     | 81  |    2 |
|     8 | b2     | 82  |    2 |
|     9 | b3     | 83  |    2 |
|    10 | b4     | 84  |    2 |
|    11 | b5     | 85  |    2 |
|    12 | b6     | 86  |    2 |
+-----------+-------------+-------+---------+
12 rows in set

mysql> select * from students a where a.classid = any (select distinct b.classid from classes b where b.classid <3);
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
|     1 | brand    | 97.5 |    1 |
|     2 | helen    | 96.5 |    1 |
|     3 | lyn     | 96  |    1 |
|     4 | sol     | 97  |    1 |
|     5 | weng    | 100  |    1 |
|     6 | diny    | 92.7 |    1 |
|     7 | b1     | 81  |    2 |
|     8 | b2     | 82  |    2 |
|     9 | b3     | 83  |    2 |
|    10 | b4     | 84  |    2 |
|    11 | b5     | 85  |    2 |
|    12 | b6     | 86  |    2 |
+-----------+-------------+-------+---------+
12 rows in set

mysql> select * from students a where a.classid <> all (select distinct b.classid from classes b where b.classid <3);
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
|    13 | c1     | 71  |    3 |
|    14 | c2     | 72.5 |    3 |
|    15 | c3     | 73  |    3 |
|    16 | c4     | 74  |    3 |
|    17 | c5     | 75  |    3 |
|    18 | c6     | 76  |    3 |
+-----------+-------------+-------+---------+
6 rows in set

mysql> select * from students a where (a.studentid, a.score) in (select max(studentid),min(score) from students);
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
|    19 | lala    | 51  |    0 |
+-----------+-------------+-------+---------+
1 row in set

mysql> select classname from classes a where exists(select 1 from students b where b.classid = a.classid);

+-----------+
| classname |
+-----------+
| 初三一班 |
| 初三二班 |
| 初三三班 |
+-----------+
3 rows in set

mysql> select classname from classes a where a.classid in(select classid from students);

+-----------+
| classname |
+-----------+
| 初三一班 |
| 初三二班 |
| 初三三班 |
+-----------+
3 rows in set

 select cname1,cname2 from tname where condition1
 union
 select cname1,cname2 from tname where condition2

 select t1_cname1,t1_cname2 from tname1 where condition
 union
 select t2_cname1,t_2cname2 from tname2 where condition