选择 group by 子句中的前 n 行 [英] Selecting the top n rows within a group by clause
问题描述
我的架构类似于以下内容:
I have schema similar to the following:
create table bar
(
instrument varchar(255) not null,
bar_dttm datetime not null,
bar_open int not null,
bar_close int not null
)
我想查询表格,并返回每个工具最近的 5 行.
I would like to query the table, and return the most recent 5 rows per instrument.
我可以逐个乐器地进行,使用:
I can do it instrument by instrument, with:
select top 5 instrument, bar_dttm, bar_open, bar_close
from bar
where instrument = 'XXX'
order by bar_dttm desc
我想在一个查询中一次为所有仪器执行此操作.这可能吗?我正在运行 SQL Server 2008.
I'd like to do this for all instruments at once in one query. Is this possible? I am running SQL Server 2008.
推荐答案
CROSS APPLY 是您通常执行此操作的方式 - http://msdn.microsoft.com/en-us/library/ms175156.aspx
CROSS APPLY is how you usually do this - http://msdn.microsoft.com/en-us/library/ms175156.aspx
编辑 - 添加示例,如下所示:
EDIT - add example, something like this:
select
bar1.instrument
,bar2.*
from (
select distinct instrument from bar) as bar1
cross apply (
select top 5
bar2.instrument
,bar2.bar_dttm
,bar2.bar_open
,bar2.bar_close
from bar as bar2 where bar2.instrument = bar1.instrument) as bar2
通常您希望在那里添加订单.
Typically you would want to add an order by in there.
编辑 - 为查询添加了不同的内容,希望能满足您的需求.编辑 - 在顶部添加了缺少的选择"关键字.复制&粘贴错误 FTL!
Edit - added distinct to the query, hopefully that gives you want you want. Edit - added missing 'select' keyword at top. copy & paste bug FTL!
这篇关于选择 group by 子句中的前 n 行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!