您可以尝试这种方法。 这个查询为每个国家返回10个人口最多的城市。

   SELECT city, country, population
   FROM
   (SELECT city, country, population, 
   @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
   @current_country := country 
   FROM cities
   ORDER BY country, population DESC
   ) ranked
   WHERE country_rank <= 10;

SELECT r.*
FROM
(
    SELECT
        r.*,
        ROW_NUMBER() OVER(PARTITION BY r.[SectionID]
                          ORDER BY r.[DateEntered] DESC) rn
    FROM [Records] r
) r
WHERE r.rn <= 10
ORDER BY r.[DateEntered] DESC

我知道这个帖子有点老了，但我刚刚遇到了一个类似的问题(从每个类别中选择最新的文章)，这是我想出的解决方案:

WITH [TopCategoryArticles] AS (
    SELECT 
        [ArticleID],
        ROW_NUMBER() OVER (
            PARTITION BY [ArticleCategoryID]
            ORDER BY [ArticleDate] DESC
        ) AS [Order]
    FROM [dbo].[Articles]
)
SELECT [Articles].* 
FROM 
    [TopCategoryArticles] LEFT JOIN 
    [dbo].[Articles] ON
        [TopCategoryArticles].[ArticleID] = [Articles].[ArticleID]
WHERE [TopCategoryArticles].[Order] = 1

这与Darrel的解决方案非常相似，但克服了可能返回比预期更多行的RANK问题。

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

UNION操作符对您有用吗?每个部分有一个SELECT，然后将它们联合在一起。不过，我猜它只适用于固定数量的部分。

我是这样做的:

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