不使用SQL中的聚合函数的最高/最低值[关闭](Highest/lowest value without using aggregate functions in SQL [closed])

不使用SQL中的聚合函数的最高/最低值[关闭](Highest/lowest value without using aggregate functions in SQL [closed])

我有一个名为department的表，它有以下数据

DNO DNAME SALARY 20 EE 30000 10 DoC 50000 30 ITS 20000

我想选择具有最高和最低工资的员工，而不使用GROUP函数或top-n分析或NOT EXISTS命令。 任何帮助将不胜感激。 谢谢

I have a table named department, which has following data

DNO DNAME SALARY 20 EE 30000 10 DoC 50000 30 ITS 20000

I want to select employee with maximum and minimum salary WITHOUT using GROUP functions or top-n analysis or NOT EXISTS command. Any help will be appreciated. Thanks

最满意答案

最多 薪水：

SELECT * FROM department d1 WHERE salary > ALL (SELECT d2.salary FROM department d2 WHERE d2.dno <> d1.dno)

最低工资：

SELECT * FROM department d1 WHERE salary < ALL (SELECT d2.salary FROM department d2 WHERE d2.dno <> d1.dno)

两种解决方案都假设薪水不能为空

For max. salary:

SELECT * FROM department d1 WHERE salary > ALL (SELECT d2.salary FROM department d2 WHERE d2.dno <> d1.dno)

For min salary:

SELECT * FROM department d1 WHERE salary < ALL (SELECT d2.salary FROM department d2 WHERE d2.dno <> d1.dno)

Both solutions assume that salary can not be null