postgresql 在 SQL 中不使用聚合函数的最高/最低值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9554347/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Highest/lowest value without using aggregate functions in SQL
提问by user841852
I have a table named department, which has following data
我有一个名为 的表department，其中包含以下数据
DNO DNAME SALARY
20 EE 30000
10 DoC 50000
30 ITS 20000
I want to select employee with maximum and minimum salary WITHOUT using GROUPfunctions or top-n analysis or NOT EXISTScommand. Any help will be appreciated. Thanks
我想在不使用GROUP函数或 top-n 分析或NOT EXISTS命令的情况下选择具有最高和最低工资的员工。任何帮助将不胜感激。谢谢
回答by a_horse_with_no_name
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
两种解决方案都假设工资不能为空
回答by Naltharial
You can do it just with joins, if you don't want to use any of the more complex operators.
如果您不想使用任何更复杂的运算符，则可以仅使用连接来完成。
SELECT *
FROM SO.dbo.MaxNoAgg mna1
LEFT JOIN SO.dbo.MaxNoAgg mna2 ON (mna2.salary > mna1.salary)
WHERE mna2.mna_id IS NULL;
That will basically give you the row for which no row with a greater salary exists. Of course, it still gives all the rows with the maximal value (SELECT DISTINCT mna1.salary).
这基本上将为您提供不存在更高薪水的行。当然，它仍然给出了最大值 ( SELECT DISTINCT mna1.salary) 的所有行。
回答by David Brabant
set nocount on
declare @table table (
val int
)
insert @table values (17)
insert @table values (31)
insert @table values (8)
insert @table values (79)
insert @table values (25)
insert @table values (13)
select * from @table
select * from @table t where 1 = (select count(distinct val) from @table where t.val >= val)
select * from @table t where 1 = (select count(distinct val) from @table where t.val <= val)
