SQL实战：子查询的应用实例

• 本实例设计到的SQL语法包括：

select ,
delete,
distict,
not exists,
not in,
group by,
having,
min,
max

• 创建成绩表：

CREATE TABLE score(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(100) NOT NULL,
course VARCHAR(40) NOT NULL,
score INT,
PRIMARY KEY ( id )
);

• 插入数据：

INSERT INTO score 
(NAME,course,score) VALUES
('张三','语文','81'),
('张三','数学','75'),
('李四','语文','56'),
('李四','数学','76'),
('李四','数学','76'),
('王五','语文','89'),
('王五','语文','89'),
('马六','数学','99')
;

• 问题1： 用一条SQL 语句,查询出每门课都大于80分的学生姓名.

答：

1.
SELECT DISTINCT NAME FROM score s WHERE
NOT EXISTS
(SELECT 1 FROM score si WHERE si.score<80 AND si.name=s.name); 
2.
SELECT DISTINCT NAME FROM score WHERE NAME 
NOT IN 
(SELECT DISTINCT NAME FROM score WHERE score<=80);
3.
SELECT NAME FROM score GROUP BY NAME HAVING MIN(score)>80; 

• 问题2： 删除除id不同, 其他都相同的冗余信息。

答：
因为MySQL语法规定，在同一语句中，不能先SELECT出同一表中的某些值，再UPDATE这个表（You can NOT specify target TABLE 'score' FOR UPDATE IN FROM clause。），因此需要写两个子查询。
用NOT IN或NOT EXISTS都可以达到目的，MIN或MAX函数确定保留冗余数据中最小或最大的那一条数据。

1.
DELETE FROM score 
WHERE id NOT IN
(
SELECT id FROM
(SELECT MIN(si.id) FROM score si GROUP BY si.name,si.course,si.score) ss
)
;
2.
DELETE s FROM score s
WHERE NOT EXISTS
(
 SELECT idi FROM
 (SELECT MAX(si.id) idi FROM score si GROUP BY si.name,si.course,si.score) ss
 WHERE s.id=ss.idi
)
;