MySQL 子查詢和分組查詢

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

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

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

/*學(xué)生表：這邊假設(shè)學(xué)生id和姓名都具有唯一性*/

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
 `studentid` int primary key NOT NULL AUTO_INCREMENT comment '學(xué)生id',
 `studentname` varchar(20) DEFAULT NULL comment '學(xué)生姓名',
 `score` DECIMAL(10,2) DEFAULT NULL comment '畢業(yè)成績(jī)',
 `classid` int(4) DEFAULT NULL comment '所屬班級(jí)id，來(lái)源于classes表的classid'
) ENGINE=InnoDB comment '學(xué)生表';
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);


/*畢業(yè)考核分?jǐn)?shù)排名表*/
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores`(
 `scoregrad` varchar(3) primary key comment '等級(jí)：S、A、B、C、D',
 `downset` int comment '分?jǐn)?shù)評(píng)級(jí)下限',
 `upset` int comment '分?jǐn)?shù)評(píng)級(jí)上限'
) comment '畢業(yè)考核分?jǐn)?shù)排名表';
INSERT INTO `scores` values ('S', 91, 100),('A', 81, 90),('B', 71, 80),('C', 61, 70),('D', 51,60);

mysql> select a.classid as 班級(jí)編號(hào),a.classname as 班級(jí)名稱,
(select count(*) from students b where b.classid = a.classid) as 學(xué)生數(shù)量
from classes a;
+----------+----------+----------+
| 班級(jí)編號(hào) | 班級(jí)名稱 | 學(xué)生數(shù)量 |
+----------+----------+----------+
|    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 班級(jí);
+----------+
| 班級(jí)   |
+----------+
| 初三一班 |
+----------+
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 班級(jí)id,a.avgscore 平均畢業(yè)分?jǐn)?shù),b.scoregrad 分?jǐn)?shù)評(píng)級(jí) 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;

+--------+--------------+----------+
| 班級(jí)id | 平均畢業(yè)分?jǐn)?shù) | 分?jǐn)?shù)評(píng)級(jí) |
+--------+--------------+----------+
|   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