SQL 为每个类别选择前 10 条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/176964/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select top 10 records for each category
提问by jbcedge
I want to return top 10 records from each section in one query. Can anyone help with how to do it? Section is one of the columns in the table.
我想在一个查询中从每个部分返回前 10 条记录。任何人都可以帮助如何做到这一点？Section 是表中的列之一。
Database is SQL Server 2005. I want to return the top 10 by date entered. Sections are business, local, and feature. For one particular date I want only the top (10) business rows (most recent entry), the top (10) local rows, and the top (10) features.
数据库是 SQL Server 2005。我想按输入的日期返回前 10 名。部分是业务、本地和功能。对于某个特定日期，我只需要前 (10) 个业务行（最新条目）、前 (10) 个本地行和前 (10) 个功能。
回答by Darrel Miller
If you are using SQL 2005 you can do something like this...
如果您使用的是 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
If your RankCriteria has ties then you may return more than 10 rows and Matt's solution may be better for you.
如果您的 RankCriteria 有联系，那么您可能会返回超过 10 行，Matt 的解决方案可能更适合您。
回答by Phil Rabbitt
In T-SQL, I would do:
在 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
回答by Matt Hamilton
This works on SQL Server 2005 (edited to reflect your clarification):
这适用于 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
回答by 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
回答by Bill Karwin
I do it this way:
我这样做：
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;
update:This example of GROUP BY works in MySQL and SQLite only, because those databases are more permissive than standard SQL regarding GROUP BY. Most SQL implementations require that all columns in the select-list that aren't part of an aggregate expression are also in the GROUP BY.
更新：这个 GROUP BY 示例仅适用于 MySQL 和 SQLite，因为这些数据库在 GROUP BY 方面比标准 SQL 更宽松。大多数 SQL 实现要求选择列表中不属于聚合表达式的所有列也在 GROUP BY 中。
回答by Vadim Loboda
If we use SQL Server >= 2005, then we can solve the task with one selectonly:
如果我们使用 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;
回答by Diadistis
I know this thread is a little bit old but I've just bumped into a similar problem (select the newest article from each category) and this is the solution I came up with :
我知道这个线程有点旧，但我刚刚遇到了一个类似的问题（从每个类别中选择最新的文章），这是我想出的解决方案：
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
This is very similar to Darrel's solution but overcomes the RANK problem that might return more rows than intended.
这与 Darrel 的解决方案非常相似，但克服了可能返回比预期更多行的 RANK 问题。
回答by Blorgbeard is out
If you know what the sections are, you can do:
如果您知道这些部分是什么，您可以执行以下操作：
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
回答by Raghu S
Tried the following and it worked with ties too.
尝试了以下方法，它也适用于关系。
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
回答by Craig
If you want to produce output grouped by section, displaying only the top nrecords from each section something like this:
如果要生成按部分分组的输出，则只显示每个部分的前n条记录，如下所示：
SECTION SUBSECTION
deer American Elk/Wapiti
deer Chinese Water Deer
dog Cocker Spaniel
dog German Shephard
horse Appaloosa
horse Morgan
...then the following should work pretty generically with all SQL databases. If you want the top 10, just change the 2 to a 10 toward the end of the query.
...那么以下内容应该适用于所有 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;
To set up:
建立：
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';
