在having子句中使用MIN函数

I want to get the name of the employee who has the minimum salary. Is there a way to do this using only one query? I have given my query below, it doesn't work because the having clause requires a condition. Is there any way to give a condition in the having clause that will retreive the employee name with the minimum salary?

我想得到最低工资的员工的姓名。有没有办法只使用一个查询？我在下面给出了我的查询，它不起作用，因为having子句需要一个条件。有没有办法在having子句中给出一个条件，用最低工资来检索员工姓名？

SELECT first_name,min(salary) as "sal"
FROM Employees
GROUP BY first_name 
having min(salary);

6 个解决方案

#1

How about using ROWNUM?

如何使用ROWNUM？

SELECT *
FROM(SELECT first_name, salary
     FROM Employees
     ORDER BY salary
) WHERE ROWNUM = 1

#2

SELECT first_name, salary  as "sal" 
FROM   employees
WHERE  salary =(SELECT MIN(salary) 
                FROM   employees);

#3

Try this solution, inspired from here:

尝试这个解决方案，灵感来自这里：

SELECT e1.first_name, e1.salary AS "sal"
FROM Employees e1
LEFT OUTER JOIN Employees e2
ON (e1.id <> e2.id AND e1.salary > e2.salary)
WHERE e2.id IS NULL;

#4

SELECT TOP 1 WITH TIES *
FROM employees
ORDER BY salary ASC

#5

With a single SELECT statement:

使用单个SELECT语句：

SELECT MIN( first_name ) KEEP ( DENSE_RANK FIRST ORDER BY salary ASC, first_name ASC ) AS first_name,
       MIN( salary     ) KEEP ( DENSE_RANK FIRST ORDER BY salary ASC, first_name ASC ) AS salary
FROM   Employees;

SQLFIDDLE

SQLFIDDLE

However, if there are multiple people with the same minimum salary then this will only get the one with the name which is first alphabetically.

但是，如果有多个人具有相同的最低工资，那么这将只获得名称首先按字母顺序排列的人。

You can get all the names, but it does require multiple SELECT statements:

您可以获取所有名称，但它确实需要多个SELECT语句：

SELECT first_name, salary
FROM   Employees
WHERE  salary = ( SELECT MIN(salary) FROM Employees ); 

But having multiple SELECT statements isn't a bad thing.

但是拥有多个SELECT语句并不是一件坏事。

SQLFIDDLE

SQLFIDDLE

#6

If you need the employee with the lowest salary why don't you use Order By ..

如果您需要薪水最低的员工，为什么不使用Order By ..

SELECT top 1 first_name,min(salary) as LowestSalary
FROM Employees order by Salary asc