一些比较难的SQL语句查询

1查询各科成绩都高于90分的人。

有一张表：

stu_course:

SELECT
	t1.stu_id,
	t1.NO 
FROM
	( SELECT stu_id, count(*) NO FROM stu_course GROUP BY stu_id ) t1
	INNER JOIN ( SELECT stu_id, count(*) NO FROM stu_course WHERE score >= 90 GROUP BY stu_id ) t2 
ON t1.stu_id = t2.stu_id AND t1.NO = t2.NO;

SELECT DISTINCT
	tb_1.stu_id,
	tb_1.NAME 
FROM
	stu_course tb_1 where ( SELECT min( tb_2.score ) FROM stu_course tb_2 WHERE tb_2.stu_id = tb_1.stu_id )>= 90;

2查询每个部门薪水最高的人

有两张表:

employees

departments

SELECT
	* 
FROM
	employees e 
WHERE
	( e.department_id, e.salary ) IN ( SELECT e1.department_id, max( e1.salary ) FROM employees e1 GROUP BY department_id );

SELECT
	* 
FROM
	employees e 
WHERE
	e.salary = ( SELECT max( e1.salary ) FROM employees e1 WHERE e1.department_id = e.department_id );

3查询每个部门薪水最高的前二人。

使用的表就是题2的表

SELECT
	* 
FROM
	employees e1 
WHERE
	( SELECT count( * ) FROM employees e2 WHERE e2.department_id = e1.department_id AND e2.salary > e1.salary ) < 2 
ORDER BY
	department_id;