不使用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 20000I 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
更多推荐
发布评论