表结构、数据:
-- 查询各科成绩都大于90的学生姓名
id name course score
1 小白 语文 91
2 小白 数学 88
3 小黑 语文 79
4 小黑 数学 92
5 小花 语文 99
6 小花 数学 95
7 小花 英语 96
方法一:
-- 采用逆向思维,先扫描表,查出有成绩小于等于90的学生姓名,
-- 然后再次扫描表,用not in 或not exists 方法。
-- not in
SELECT DISTINCT t.name FROM test t WHERE t.name NOT IN (
SELECT t.name FROM test t WHERE t.score <= 90)
-- not exists
SELECT DISTINCT t.name FROM test t WHERE NOT EXISTS (
SELECT 1 FROM test s WHERE s.score <= 90 AND s.name = t.name)
/*
exists 详解:
取出外表第一条数据,然后与内表根据连接条件,形成一条或多条数据,
判断这些生成的数据中,是否存在或者是不存在符合where条件的。
结果为ture的那条外表记录旧被查询出来!
*/
方法二:
SELECT t.name FROM test t
GROUP BY t.name HAVING MIN(t.score) > 90
方法三:
-- 如果能获得一张表,由学生姓名,语文成绩,数学成绩,英语成绩的表,
--剩下的就是在WHERE条件中筛选及可以获得想要的结果。
SELECT t.name FROM (
SELECT
s.name, s.score wy, s1.score sx, s2.score yy
FROM
test s
INNER JOIN
test s1 ON s.name = s1.name AND s.score != s1.score
INNER JOIN
test s2 ON s.name = s2.name AND s.score != s2.score
WHERE
s.score > 90 AND s1.score > 90 AND s2.score > 90
) t
GROUP BY t.name
表结构及数据:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL COMMENT '姓名',
`course` varchar(32) DEFAULT NULL COMMENT '课程',
`score` int(11) DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
insert into `test` (`id`, `name`, `course`, `score`)
values('1','小白','语文','91');
insert into `test` (`id`, `name`, `course`, `score`)
values('2','小白','数学','88');
insert into `test` (`id`, `name`, `course`, `score`)
values('3','小黑','语文','79');
insert into `test` (`id`, `name`, `course`, `score`)
values('4','小黑','数学','92');
insert into `test` (`id`, `name`, `course`, `score`)
values('5','小花','语文','99');
insert into `test` (`id`, `name`, `course`, `score`)
values('6','小花','数学','95');
insert into `test` (`id`, `name`, `course`, `score`)
values('7','小花','英语','96');