我想获取工资最低的员工姓名。有没有办法只使用一个查询就可以实现这个功能?我已经提供了我的查询语句,但是它不起作用,因为having子句需要一个条件。有没有办法在having子句中给出一个条件,以检索工资最低的员工姓名?
SELECT first_name,min(salary) as "sal"
FROM Employees
GROUP BY first_name
having min(salary);
ROWNUM
?SELECT *
FROM(SELECT first_name, salary
FROM Employees
ORDER BY salary
) WHERE ROWNUM = 1
SELECT first_name, salary as "sal"
FROM employees
WHERE salary =(SELECT MIN(salary)
FROM employees);
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;
通过单个 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;
然而,如果有多个人的最低工资相同,则只会返回按字母顺序排列最前面的人的名字。
您可以获取所有的名字,但需要多个SELECT
语句:
SELECT first_name, salary
FROM Employees
WHERE salary = ( SELECT MIN(salary) FROM Employees );
但是拥有多个SELECT
语句并不是坏事。
SELECT TOP 1 WITH TIES *
FROM employees
ORDER BY salary ASC
SELECT first_name,min(salary) as "sal"
FROM Employees
GROUP BY first_name
having min(salary) >0;
SELECT top 1 first_name,min(salary) as LowestSalary
FROM Employees order by Salary asc
SELECT e.first_name,e.salary AS "sal"
FROM Employees e, Employees e2
GROUP BY first_name
HAVING MIN(e2.salary)=e.salary;