Q)从每个组中找到TOP X记录(Oracle)

SQL> select * from emp e 
  2  where e.empno in (select d.empno from emp d 
  3  where d.deptno=e.deptno and rownum<3)
  4  order by deptno
  5  ;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

  7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
  7839 KING       PRESIDENT            17-NOV-81       5000                    10
  7369 SMITH      CLERK           7902 17-DEC-80        800                    20
  7566 JONES      MANAGER         7839 02-APR-81       2975                    20
  7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
  7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

选定6行。

Q)从每个组中找到TOP X记录(Oracle)

SQL> select * from emp e 
  2  where e.empno in (select d.empno from emp d 
  3  where d.deptno=e.deptno and rownum<3)
  4  order by deptno
  5  ;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

  7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
  7839 KING       PRESIDENT            17-NOV-81       5000                    10
  7369 SMITH      CLERK           7902 17-DEC-80        800                    20
  7566 JONES      MANAGER         7839 02-APR-81       2975                    20
  7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
  7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

选定6行。

如果你正在使用SQL 2005，你可以这样做…

SELECT rs.Field1,rs.Field2 
    FROM (
        SELECT Field1,Field2, Rank() 
          over (Partition BY Section
                ORDER BY RankCriteria DESC ) AS Rank
        FROM table
        ) rs WHERE Rank <= 10

如果你的RankCriteria有平局，那么你可能会返回超过10行，Matt的解决方案可能更适合你。

在T-SQL中，我会这样做:

WITH TOPTEN AS (
    SELECT *, ROW_NUMBER() 
    over (
        PARTITION BY [group_by_field] 
        order by [prioritise_field]
    ) AS RowNo 
    FROM [table_name]
)
SELECT * FROM TOPTEN WHERE RowNo <= 10

虽然这个问题是关于SQL Server 2005的，但大多数人已经继续前进了，如果他们确实发现了这个问题，在其他情况下首选的答案可能是使用CROSS APPLY，就像这篇博客文章中所说明的那样。

SELECT *
FROM t
CROSS APPLY (
  SELECT TOP 10 u.*
  FROM u
  WHERE u.t_id = t.t_id
  ORDER BY u.something DESC
) u

该查询涉及两个表。OP的查询只涉及1个表，在这种情况下，基于窗口函数的解决方案可能更有效。

如果我们使用SQL Server >= 2005，那么我们可以只用一个选择来解决任务:

declare @t table (
    Id      int ,
    Section int,
    Moment  date
);

insert into @t values
(   1   ,   1   , '2014-01-01'),
(   2   ,   1   , '2014-01-02'),
(   3   ,   1   , '2014-01-03'),
(   4   ,   1   , '2014-01-04'),
(   5   ,   1   , '2014-01-05'),

(   6   ,   2   , '2014-02-06'),
(   7   ,   2   , '2014-02-07'),
(   8   ,   2   , '2014-02-08'),
(   9   ,   2   , '2014-02-09'),
(   10  ,   2   , '2014-02-10'),

(   11  ,   3   , '2014-03-11'),
(   12  ,   3   , '2014-03-12'),
(   13  ,   3   , '2014-03-13'),
(   14  ,   3   , '2014-03-14'),
(   15  ,   3   , '2014-03-15');


-- TWO earliest records in each Section

select top 1 with ties
    Id, Section, Moment 
from
    @t
order by 
    case 
        when row_number() over(partition by Section order by Moment) <= 2 
        then 0 
        else 1 
    end;


-- THREE earliest records in each Section

select top 1 with ties
    Id, Section, Moment 
from
    @t
order by 
    case 
        when row_number() over(partition by Section order by Moment) <= 3 
        then 0 
        else 1 
    end;


-- three LATEST records in each Section

select top 1 with ties
    Id, Section, Moment 
from
    @t
order by 
    case 
        when row_number() over(partition by Section order by Moment desc) <= 3 
        then 0 
        else 1 
    end;