sql - 返回每组 Oracle SQL 的前 n 条记录

Question

这个问题在这里已经有了答案:





Select top 10 records for each category

(14 个回答)


5年前关闭。




我的问题

我想在 Oracle 10g 中返回按日期排序的每组前 n 行

我的 table

EMPLOYEE|START_DATE|DEPARTMENT
Amy     |01-02-1901|Sales
Edwina  |01-02-1902|Mergers
Tawnee  |01-02-1904|Legal
Trudy   |01-02-1998|Sales
Tanner  |01-02-1967|Sales
Kelly   |01-02-1954|Mergers
Jenny   |01-02-1991|Sales
Jacinta |01-02-1924|Legal
Suzanne |01-02-1976|Legal
Jacqui  |01-02-1989|Legal
Jill    |01-02-1989|Mergers
Kate    |01-02-1998|Mergers
Jane    |01-02-1900|Sales
Louise  |01-02-1912|Mergers
Kim     |01-02-1976|Sales
Cara    |01-02-1955|Sales
Kirsten |01-02-1933|Legal
Sarah   |01-02-1998|Legal

期望的结果

EMPLOYEE|START_DATE|DEPARTMENT
Jane    |01-02-1900|Sales
Amy     |01-02-1901|Sales
Tawnee  |01-02-1904|Legal
Jacinta |01-02-1924|Legal
Sarah   |01-02-1998|Legal
Edwina  |01-02-1902|Mergers
Louise  |01-02-1912|Mergers

我试过的

(select * from 
employees where 
DEPARTMENT = 'Sales' and
rownum <3;)
UNION
(select * from 
employees where 
DEPARTMENT = 'Legal' and
rownum <3;)
UNION
(select * from 
employees where 
DEPARTMENT = 'Mergers' and
rownum <3;)

非常丑陋的查询

我在想是否有办法让你

OVER (PARTITION BY DEPARTMENT)

但从我读到的，这需要前面有一个解析函数(计数，总和)。有没有更优雅、更便宜的解决方案？

Answer 1

考虑这种使用计数相关聚合查询的非 Windows 函数方法。这个想法是运行一个部门等级子查询，然后在派生表中使用它，该表根据这个部门等级过滤外部查询。请注意，您想要的结果不会按订单返回 START_DATE但只是查询的行号。

SELECT main.EMPLOYEE, t.START_DATE, t.DEPARTMENT
FROM 
   (SELECT t.EMPLOYEE, t.START_DATE, t.DEPARTMENT, 
          (SELECT Count(*) FROM Employees sub 
           WHERE sub.START_DATE <= t.START_DATE 
           AND sub.Department = t.Department) AS DeptRank
FROM Employees t) main
WHERE main.DeptRank <= 3
ORDER BY main.DEPARTMENT, main.START_DATE;

-- EMPLOYEE START_DATE  DEPARTMENT
-- Tawnee   1/2/1904    Legal
-- Jacinta  1/2/1924    Legal
-- Kirsten  1/2/1933    Legal
-- Edwina   1/2/1902    Mergers
-- Louise   1/2/1912    Mergers
-- Kelly    1/2/1954    Mergers
-- Jane     1/2/1900    Sales
-- Amy      1/2/1901    Sales
-- Cara     1/2/1955    Sales

对于 Windows 函数对应:

SELECT main.EMPLOYEE, t.START_DATE, t.DEPARTMENT
FROM 
   (SELECT t.EMPLOYEE, t.START_DATE, t.DEPARTMENT, 
           RANK() OVER (PARTITION BY Department
           ORDER BY START_DATE) AS DeptRank
FROM Employees t) main
WHERE main.DeptRank <= 3
ORDER BY main.DEPARTMENT, main.START_DATE;

正如@Matt 评论的那样，您可能想要处理关系(即同一天开始的员工)。上述两种解决方案都将根据等级过滤器输出所有此类员工。要获取相关子查询中的关系之一，请使用员工姓名作为决胜局(或者最好使用唯一 ID，如果可用):

SELECT main.EMPLOYEE, t.START_DATE, t.DEPARTMENT
FROM 
   (SELECT t.EMPLOYEE, t.START_DATE, t.DEPARTMENT, 
           (SELECT Count(*) FROM Employees sub 
            WHERE sub.Department =  t.Department 
             AND (sub.START_DATE <= t.START_DATE
                  OR sub.START_DATE = t.START_DATE 
                  AND sub.EMPLOYEE < t.EMPLOYEE) AS DeptRank
FROM Employees t) main
WHERE main.DeptRank <= 3
ORDER BY main.DEPARTMENT, main.START_DATE;

对于窗口函数查询使用 ROW_NUMBER()代替 RANK() .