- 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;
- --该片段来自于http://www.codesnippet.cn/detail/1908201513500.html
来源: http://www.codesnippet.cn/detail/1908201513500.html