数据库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)--1select 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');--2select * 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' );--3select * from sc where c_id = '01' and s_id not in (select distinct s_id from sc where c_id = '02');--4select 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');--5select 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;--6select 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;--7select 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;