选择 group by 子句中的前 n 行

本文介绍了选择 group by 子句中的前 n 行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的架构类似于以下内容:

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 是您通常执行此操作的方式 - msdn.microsoft/en-us/library/ms175156.aspx

CROSS APPLY is how you usually do this - msdn.microsoft/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!