mysql - 从MYSQL中工资第三高的员工表中获取所有员工工资

Question

已编写以下查询:

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

给出以下错误:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Answer 1

您的查询中有 2 个问题。首先是语法，第二是 LIMIT SELECT 中没有 ORDER BY。如果没有 ORDER，结果每次都会改变(Ramdom)

您可以使用如下查询来完成此操作:

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)