为每个类别选择前10条记录

我想在一个查询中返回每个部分的前10条记录.任何人都可以帮忙解决这个问题吗？Section是表中的一列.

数据库是SQL Server 2005.我想按输入的日期返回前10名.部分是业务,本地和功能.对于一个特定日期,我只想要前(10)个业务行(最近的条目),前(10)个本地行和前(10)个特征.

1> Darrel Mille..：

---

如果你使用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的解决方案可能对您更好.

---

如果你真的只想要前10名,请将其更改为RowNumber()而不是Rank().没关系.

---

对于使用Sql Server的任何人,Mike L提到的RowNumber()函数是ROW_NUMBER().

---

这有效,但请注意,如果没有索引的*first*key是RankCriteria,则rank()可能会被查询规划器转换为完整的表排序.在这种情况下,您可以获得更好的里程数,选择不同的部分并交叉申请,以挑选RankCriteria desc排序的前10名.

2> 小智..：

---

在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

3> Matt Hamilto..：

---

这适用于SQL Server 2005(编辑以反映您的澄清):

select *
from Things t
where t.ThingID in (
    select top 10 ThingID
    from Things tt
    where tt.Section = t.Section and tt.ThingDate = @Date
    order by tt.DateEntered desc
    )
    and t.ThingDate = @Date
order by Section, DateEntered desc

---

但是,对于Section为null的行,这不起作用.你需要说"where(tt.Section为null,t.Section为null)或tt.Section = t.Section"

4> lorond..：

---

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

5> Bill Karwin..：

---

我是这样做的:

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实现要求select-list中不属于聚合表达式的所有列也在GROUP BY中.

6> Vadim Loboda..：

---

如果我们使用SQL Server> = 2005,那么我们只能用一个select来解决任务:

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;

---

TOP 1在这里与WITH TIES合作.WITH TIES表示当ORDER BY = 0时,SELECT接受此记录(因为TOP 1)和所有其他ORDER BY = 0(因为WITH TIES)

7> Blorgbeard..：

---

如果您知道这些部分是什么,您可以:

select top 10 * from table where section=1
union
select top 10 * from table where section=2
union
select top 10 * from table where section=3

---

这将是最简单的方法.

---

但是如果你有150或者类别按日,周等变化,这将是低效的.

8> Diadistis..：

---

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

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问题.

9> 小智..：

---

尝试了以下内容,它也与关系有效.

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

---

重复别人的回答有什么意义？