数据库SQL学习的经典案例：学生专业老师分数表的操练

数据库SQL学习的经典案例：学生专业老师分数表的操练；针对一个问题------选了课程1而没有选课程2的学生有哪些？ 下面的SQL语句是各个不同的写法，整理下～ 无 mysql desc SC;+-------+---------------+------+-----+---------+-------+| Field | Type | Null

数据库SQL学习的经典案例：学生专业老师分数表的操练；针对一个问题------选了课程1而没有选课程2的学生有哪些？

下面的SQL语句是各个不同的写法，整理下～

mysql> desc SC;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| S_id  | varchar(10)   | YES  |     | NULL    |       |
| C_id  | varchar(10)   | YES  |     | NULL    |       |
| score | decimal(18,1) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from SC;
+------+------+-------+
| S_id | C_id | 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 |
| 08   | 04   |  79.0 |
| 11   | 03   |  77.9 |
| 12   | 02   |  47.9 |
| 12   | 04   |  47.9 |
| 11   | 01   |  77.9 |
| 01   | 04   |  73.9 |
| 01   | 05   |  83.9 |
| 06   | 04   |  75.0 |
| 06   | 05   |  85.0 |
| 11   | 05   |  81.0 |
| 11   | 04   |  91.0 |
+------+------+-------+
29 rows in set (0.00 sec)


--1
select B.* from SC B where B.C_id = '01' 
and not exists(select * from SC B2 where B.S_id = B2.S_id and B2.C_id = '02');

--2
select * from 
(select B.S_id, B.C_id aC_id, B.score ascore, B2.C_id bC_id, B2.score bscore 
from SC B inner join SC B2 
on B.S_id = B2.S_id and B.C_id = '01' and B2.C_id != '01') 
BBB 
where not exists 
(select * from (select B.S_id, B.C_id aC_id, B.score ascore, B2.C_id bC_id, B2.score bscore 
from SC B inner join SC B2 
on B.S_id = B2.S_id and B.C_id = '01' and B2.C_id != '01') 
CCC where BBB.S_id = CCC.S_id and CCC.bC_id = '02' );

--3
select * from SC where C_id = '01' 
and S_id not in 
(select distinct S_id from SC where C_id = '02');

--4
select B.S_id, B.C_id aC_id, B.score ascore, B2.C_id bC_id, B2.score bscore 
from SC B inner join SC B2 
on B.S_id = B2.S_id and B.C_id = '01' and B2.C_id != '01' 
group by S_id having (aC_id, bC_id) != ('01','02');

--5
select B.*, B2.* from SC B left join SC B2 
on B.S_id = B2.S_id and B2.C_id = '02' 
where B.C_id = '01' and B2.C_id is null;

--6
select B.*, B2.* from SC B right join SC B2 
on B.S_id = B2.S_id and B.C_id = '02' 
where B2.C_id = '01' and B.C_id is null;

--7
select B.S_id, B.C_id aC_id, B.score ascore, B2.C_id bC_id, B2.score bscore 
from SC B inner join SC B2 
on B.S_id = B2.S_id and B.C_id = '01' 
and B2.C_id != '01' 
group by S_id having nullif(bC_id, '02') >> 1;