mysql - 从员工表中从MySQL中的第三高薪中获取所有员工薪水

下面写了查询：

mysql > select * from employees where Salary NOT IN (select Salary from employees limit 3);

给出以下错误：


  错误1235（42000）：此版本的MySQL尚不支持'LIMIT＆
  IN / ALL / ANY / SOME子查询”

最佳答案

您的查询中有2个问题。第一个是语法，第二个是LIMIT SELECT中没有ORDER BY。如果没有ORDER，结果每次都可以更改（随机）

您可以使用以下查询来做到这一点：

SELECT e.* FROM employees e WHERE e.salery NOT IN
(SELECT * FROM (
    SELECT s.salery
    FROM employees s
    ORDER BY salery LIMIT 3) AS tmp)
ORDER BY e.salery ;

这是我的样本：

MariaDB [bernd]> select * FROM employees;
+----+--------+--------+
| id | salery | name   |
+----+--------+--------+
|  1 |    100 | Bernd  |
|  2 |    500 | Peter  |
|  3 |    300 | Paul   |
|  4 |   1234 | Mary   |
|  5 |    800 | Erwin  |
|  6 |    777 | Hubert |
+----+--------+--------+
6 rows in set (0.00 sec)

MariaDB [bernd]>

MariaDB [bernd]> SELECT e.* FROM employees e WHERE e.salery NOT IN
    -> (SELECT * FROM (
    ->     SELECT s.salery
    ->     FROM employees s
    ->     ORDER BY salery LIMIT 3) AS tmp)
    -> ORDER BY e.salery ;
+----+--------+--------+
| id | salery | name   |
+----+--------+--------+
|  6 |    777 | Hubert |
|  5 |    800 | Erwin  |
|  4 |   1234 | Mary   |
+----+--------+--------+
3 rows in set (0.00 sec)