选择加入的SQL Server前3名

I am trying to extract the Top 3 of each group in a select with multiple joins. The Top 3 should group by the email address and symbol_short field (stocks). In the data I have 1 record where the symbol_short is Bitcoin, 4 records with XBY, and 1 with XVG for j@doe and 4 XBY for s@doe.

我试图在具有多个连接的选择中提取每个组的前3个。前3名应按电子邮件地址和symbol_short字段（股票）分组。在数据中我有1条记录，其中symbol_short是比特币，4条记录带有XBY，1条用XVG表示j @ doe，4条XBY表示s @ doe。

It looks something like this (other columns removed for simplicity)

它看起来像这样（为简单起见，删除了其他列）

email      | symbol_short
-----------+-------------
 [e-mail] | Bitcoin     
 [e-mail] | XBY
 [e-mail] | XBY
 [e-mail] | XBY    
 [e-mail] | XBY    
 [e-mail] | XVG
 [e-mail] | XBY
 [e-mail] | XBY    
 [e-mail] | XBY    
 [e-mail] | XBY

It should return the Top 3 of symbol_short grouped by the email address like this:

它应该返回按电子邮件地址分组的symbol_short的前3个，如下所示：

 email     | symbol_short
-----------+-------------
 [e-mail] | Bitcoin     
 [e-mail] | XBY
 [e-mail] | XBY
 [e-mail] | XBY   
 [e-mail] | XVG
 [e-mail] | XBY
 [e-mail] | XBY    
 [e-mail] | XBY    

This is the select which currently returns all rows.

这是当前返回所有行的选择。

SELECT        
c_connection.email, c_alert.exchange, c_alert.symbol_short, c_alert.price_usd, 
c_history.alert_price, c_history.current_price, c_history.dynamic,
c_history.positive_change, c_history.negative_change, c_history.created, c_connection.username
FROM 
c_alert INNER JOIN c_history ON c_alert.id = c_history.id_alert INNER JOIN c_connection ON c_alert.login_key = c_connection.login_key
ORDER BY c_connection.email, c_alert.symbol_short, c_history.created DESC

Is it possible to get the Top 3 for the email, symbol_short group? 2 field solution would be great, potentially I would need all the 'select' fields as shown above in the result. Thanks!

是否可以获得电子邮件symbol_short组的前三名？ 2字段解决方案会很棒，可能我需要所有'select'字段，如结果中所示。谢谢！

Final query, tweaked based on GordonLinoff's answer:

根据GordonLinoff的回答调整最终查询：

with t as (

      SELECT        
c_connection.email, c_alert.exchange, c_alert.symbol_short, c_alert.price_usd, 
c_history.alert_price, c_history.current_price, c_history.dynamic,
c_history.positive_change, c_history.negative_change, c_history.created, c_connection.username
FROM 
c_alert INNER JOIN c_history ON c_alert.id = c_history.id_alert INNER JOIN c_connection ON c_alert.login_key = c_connection.login_key
-- ORDER BY c_connection.email, c_alert.symbol_short, c_history.created DESC

     )
select t.*
from (select t.*, row_number() over (partition by t.email, t.symbol_short order by t.email) as seqnum
      from t
     ) t
where seqnum <= 3;

1 个解决方案

#1

Hmmm . . . I'm thinking that you want at most three of any symbol for a given email. If so:

嗯。 。 。我认为你最多需要三个给定电子邮件的符号。如果是这样：

with t as (
      <your query here>
     )
selecct t.*
from (select t.*, row_number() over (partition by email, symbol_short order by email) as seqnum
      from t
     ) t
where seqnum <= 3;