尝试了下面的方法，它也适用于领带。

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

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行。

我是这样做的:

SELECT a.* FROM articles AS a
  LEFT JOIN articles AS a2 
    ON a.section = a2.section AND a.article_date <= a2.article_date
GROUP BY a.article_id
HAVING COUNT(*) <= 10;

更新:这个GROUP BY的例子只适用于MySQL和SQLite，因为这些数据库在GROUP BY方面比标准SQL更允许。大多数SQL实现要求选择列表中不属于聚合表达式的所有列也在GROUP BY中。

如果你正在使用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的解决方案可能更适合你。

如果你想生成按节分组的输出，只显示每个节的前n条记录，如下所示:

SECTION     SUBSECTION

deer        American Elk/Wapiti
deer        Chinese Water Deer
dog         Cocker Spaniel
dog         German Shephard
horse       Appaloosa
horse       Morgan

.．.那么下面的代码应该适用于所有SQL数据库。如果您想要前10，只需在查询的末尾将2更改为10。

select
    x1.section
    , x1.subsection
from example x1
where
    (
    select count(*)
    from example x2
    where x2.section = x1.section
    and x2.subsection <= x1.subsection
    ) <= 2
order by section, subsection;

设置:

create table example ( id int, section varchar(25), subsection varchar(25) );

insert into example select 0, 'dog', 'Labrador Retriever';
insert into example select 1, 'deer', 'Whitetail';
insert into example select 2, 'horse', 'Morgan';
insert into example select 3, 'horse', 'Tarpan';
insert into example select 4, 'deer', 'Row';
insert into example select 5, 'horse', 'Appaloosa';
insert into example select 6, 'dog', 'German Shephard';
insert into example select 7, 'horse', 'Thoroughbred';
insert into example select 8, 'dog', 'Mutt';
insert into example select 9, 'horse', 'Welara Pony';
insert into example select 10, 'dog', 'Cocker Spaniel';
insert into example select 11, 'deer', 'American Elk/Wapiti';
insert into example select 12, 'horse', 'Shetland Pony';
insert into example select 13, 'deer', 'Chinese Water Deer';
insert into example select 14, 'deer', 'Fallow';

虽然这个问题是关于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个表，在这种情况下，基于窗口函数的解决方案可能更有效。