如何编写SQL语句以显示一个表的多个最小值并显示来自另一个表的信息？

Question

如何写一个SQL语句来显示“数学系”里所有在“数学系”里挣超过最低工资的老师的名字和薪水？

表1:教员

mysql> DESCRIBE Instructor;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| instructor_id | int         | NO   | PRI | NULL    |       |
| name          | varchar(50) | YES  |     | NULL    |       |
| salary        | int         | NO   |     | NULL    |       |
| dept_name     | varchar(50) | YES  | MUL | NULL    |       |
| course_id     | int         | NO   | MUL | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

表2:该部

mysql> DESCRIBE Department;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| dept_name   | varchar(50) | NO   | PRI | NULL    |       |
| budget      | int         | NO   |     | NULL    |       |
| building_id | int         | NO   | MUL | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

Answer 1

子选择是一种方式：

SELECT x.name, x.salary 
FROM Instructor x
WHERE salary > (SELECT MIN(salary) 
                FROM Instructor y
                WHERE x.dept_name = y.dept_name)
  AND x.dept_name = 'Mathematics'

或者，您可以将查询稍微调整为：

SELECT Instructor.name
     , Instructor.salary
     , T.min_salary
FROM Instructor
JOIN (
    SELECT MIN(salary)
    FROM Instructor
    WHERE Instructor.dept_name = 'Mathematics'
) AS T (min_salary)
    ON Instructor.salary > T.min_salary;