sql - 返回基于单个列的唯一行

Question

我有一张 table (员工)。

员工

ID   Name   Salary  Department
------------------------------
1    Steve  10000   SQ
2    Buck   15000   AS
3    Dan    10000   SQ
4    Dave   10000   AS
5    Jack   30000   AS
6    Amy    8000    GM

我需要为每个部门退回一名员工。例如，对于上述日期，我需要返回如下数据:

ID   Name   Salary  Department
------------------------------
1    Steve  10000   SQ   ( I can either return Steve or Dan for this group SQ)
2    Buck   15000   AS   ( I can either return Buck or Dave or Jack for this group AS)
6    Amy    8000    GM

Answer 1

这是 row_number() 的一个很好的用途:

select id, name, salary, department
from (select t.*,
             row_number() over (partition by department order by department) as seqnum
      from t
     ) t
where seqnum = 1

如果您想要第一个或最后一个 id，请在 row_number() 分区子句中使用 order by id asc 或 order by id desc .如果您想要随机行，请使用 order by dbms_random.value。