blocks|key|1726444|text|如果你使用的是SQL2005，你可以这样做...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1726445|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|code-block|syntax|javascript|1726446|如果您的RankCriteria是平局的，那么您可能会返回超过10行，Matt的解决方案可能更适合您。|1726447|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you are using SQL 2005 you can do something like this...

<pre><code>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 &lt;= 10
</code></pre>

If your RankCriteria has ties then you may return more than 10 rows and Matt's solution may be better for you.

blocks|key|1726550|text|在T-SQL中，我会这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1726551|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|code-block|syntax|javascript|1726552|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In T-SQL, I would do:

<pre><code>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 &lt;= 10
</code></pre>

blocks|key|1726513|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1726514|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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 &lt;= 10
ORDER BY r.[DateEntered] DESC
</code></pre>

blocks|key|1511138|text|这适用于SQL+Server+2005+(经过编辑以反映您的说明)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1511139|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|code-block|syntax|javascript|1511140|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This works on SQL Server 2005 (edited to reflect your clarification):

<pre><code>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
</code></pre>

blocks|key|1726395|text|如果你知道这些部分是什么，你可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1726396|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|code-block|syntax|javascript|1726397|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you know what the sections are, you can do:

<pre><code>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
</code></pre>

blocks|key|1726503|text|我知道这个帖子有点老了，但我刚刚遇到了一个类似的问题(从每个类别中选择最新的文章)，这是我想出的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1726504|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|code-block|syntax|javascript|1726505|这与Darrel的解决方案非常相似，但克服了可能返回比预期更多的行的排名问题。|1726506|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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 :

<pre><code>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
</code></pre>

This is very similar to Darrel's solution but overcomes the RANK problem that might return more rows than intended.

blocks|key|1511557|text|我尝试了下面的方法，它也适用于领带。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1511558|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|code-block|syntax|javascript|1511559|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Tried the following and it worked with ties too.

<pre><code>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 &lt;= 10
</code></pre>

blocks|key|1726188|text|UNION操作符可以为您工作吗？为每个部分选择一个，然后将它们合并在一起。不过，我猜它只适用于固定数量的部分。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1726189|entityMap|0|LINK|mutability|MUTABLE|url|http://www.w3schools.com/sql/sql_union.asp^0|0|5|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Might the <a href="http://www.w3schools.com/sql/sql_union.asp" rel="nofollow noreferrer">UNION</a> operator work for you? Have one SELECT for each section, then UNION them together. Guess it would only work for a fixed number of sections though.

blocks|key|1511610|text|虽然这个问题是关于SQL+Server2005的，但大多数人已经离开了这个问题，如果他们确实找到了这个问题，在其他情况下的首选答案是一个using+CROSS+APPLY+as+illustrated+in+this+blog+post。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1511611|SELECT+*
FROM+t
CROSS+APPLY+(
++SELECT+TOP+10+u.*
++FROM+u
++WHERE+u.t_id+=+t.t_id
++ORDER+BY+u.something+DESC
)+u|code-block|syntax|javascript|1511612|此查询涉及2个表。OP查询只涉及一个表，在这种情况下，基于窗口函数的解决方案可能更有效。|1511613|entityMap|0|LINK|mutability|MUTABLE|url|https://blog.jooq.org/2017/09/22/how-to-write-efficient-top-n-queries-in-sql/^0|22|B|1W|1E|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

While the question was about SQL Server 2005, most people have moved on and if they do find this question, what could be the preferred answer in other situations is one <a href="https://blog.jooq.org/2017/09/22/how-to-write-efficient-top-n-queries-in-sql/" rel="nofollow noreferrer">using <code>CROSS APPLY</code> as illustrated in this blog post</a>.

<pre class="lang-sql prettyprint-override"><code>SELECT *
FROM t
CROSS APPLY (
 SELECT TOP 10 u.*
 FROM u
 WHERE u.t_id = t.t_id
 ORDER BY u.something DESC
) u
</code></pre>

This query involves 2 tables. The OP's query only involves 1 table, in case of which a window function based solution might be more efficient.

blocks|key|1511495|text|您可以尝试这种方法。此查询返回每个国家/地区的10个人口最多的城市。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1511496|+++SELECT+city,+country,+population
+++FROM
+++(SELECT+city,+country,+population,+
+++@country_rank+:=+IF(@current_country+=+country,+@country_rank+%2B+1,+1)+AS+country_rank,
+++@current_country+:=+country+
+++FROM+cities
+++ORDER+BY+country,+population+DESC
+++)+ranked
+++WHERE+country_rank+<=+10;|code-block|syntax|javascript|1511497|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can try this approach. 
This query returns 10 most populated cities for each country.

<pre><code> 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 &lt;= 10;
</code></pre>

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.

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.

Select top 10 records for each category

SQL Server

数据库

我想在一个查询中返回每个部分的前10条记录。有没有人能帮我解决这个问题呢？节是表中的一列。数据库为SQL Server 2005。我想按输入的日期返回前10名。部分包括业务、本地和功能。对于一个特定的日期，我只需要前(10)个业务行(最近的条目)、前(10)个本地行和前(10)个特征。

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

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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