查找min(sal)和员工名称SQL

Question

我有一张名叫emp的桌子，我想找一位薪水最低的办事员.我的代码select min(sal) as min from emp where job='CLERK';工作得很好，我得到了以下信息：

  MIN
----------
       800

但是我也想显示一下职员的名字，那就是Smith。当我运行这个代码select ename, min(sal) as min from emp where job='CLERK' group by name;时，它会给我表中所有的办事员，这并不是我真正想要的。这是我桌子上的一个片段：

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);

Answer 1

尝尝这个

SELECT * FROM emp 
WHERE SAL = (select MIN(SAL) sal from emp WHERE JOB ='CLERK')
and JOB ='CLERK';

Answer 2

您可以使用row_number

select ename, sal as min
from (
  select ename, sal,
         row_number() over (order by sal) as rn  
  from emp 
  where job='CLERK' ) t
where t.rn = 1

Answer 3

您可以使用子查询和排序函数来完成这一任务：

select e.*
from (select e.*,
             dense_rank() over (partition by job order by salary) as seqnum
      from emp
      where job = 'CLERK' 
     ) e
where seqnum = 1;

如果有领带，这将返回多个行。如果您只需要一个，则可以使用row_number()而不是dense_rank()。

而且，如果要将所有办事员的名称作为单个分隔值，则可以使用list_agg()。

select listagg(e.name, ', ') within group (order by e.name) as names
from (select e.*,
             dense_rank() over (partition by job order by salary) as seqnum
      from emp
      where job = 'CLERK' 
     ) e
where seqnum = 1;