SQL组 - 限制 [英] SQL group - limit

问题描述

有时找到沟通问题的最佳方法与找到解决方案一样困难... LOL但是这样...

Sometimes finding the best way to communicate a 'problem' is as difficult as finding the solution... LOL but here goes...

我有一个表与公司，我有一个相关的表与每个公司的一些历史...
我想要返回一个查询与每个公司的最后3个'历史'条目的记录集...

I have a table with companies, I have a related table with some history for each company... I want to return a query with a record set of the last 3 'history' entries per company...

所以记录集看起来像这样...

so the recordset will look something like this...

company A
  history Az
  history Ay
  history Ax
company B
  history Bz
  history By
  history Bx
company C
  history Cz
  history Cy
  history Cx

我遇到的问题是如果我限制3 - 连接在在那里我只得到所有记录的最后3个记录，而不是每个公司的最后3个记录...

The issue I'm running into is if I LIMIT 3 - with the joins being in there I JUST get the LAST 3 records of all the records, not the last 3 for EACH company...

这是我最后一次尝试的 - 但它只是拉回来一个公司，3个记录..这就是...（IBM DB2 9 - 但唯一应该影响我的事情s的语法限制在3 ..）

Here's what I have last tried - but it's just pulling back ONE company, 3 records.. and that's it...(IBM DB2 9 - but the only thing that should affect is the syntax on the limit of 3..)

SELECT 
   C.CompanyName   
  ,H.*

FROM Companies C
JOIN CompanyTypes CT ON C.fkTypeID = CT.ID

INNER JOIN (
    SELECT   sCH.*
    FROM     CompanyHistory sCH
    ORDER BY sCH.DATE DESC 
    FETCH FIRST 3 ROWS ONLY
) H ON H.fkCompanyID = C.ID 


WHERE CT.Type = 'bookstore'

我做错了什么？
任何帮助都赞赏...提前感谢...

What am i doing wrong??? any help is appreciated... Thanks in advance....

推荐答案

SELECT C.CompanyName,H.*
FROM Companies C
JOIN CompanyTypes CT ON C.fkTypeID = CT.ID
INNER JOIN (
    SELECT   sCH.*
    FROM     CompanyHistory sCH    
    ORDER BY sCH.DATE DESC                   -- order desc so we can count
) H ON H.fkCompanyID = C.ID 
WHERE CT.Type = 'bookstore'
  and 3>(select count(*)                     -- at most 2 previous occurances
       from CompanyHistory ich
       where ich.fkCompanyID=C.ID            -- same company
          and datediff(d,ich.date,H.date)<0) -- but dates less than the row's date

基本上我计算每个公司的前几行，一旦我们超过2（+当前行做3），删除行。您需要按日期desc对 CompanyHistory 表进行排序。

Basically I'm counting the previous rows for each company, and dropping rows once we get past 2 (+the current row makes 3). You need to sort the CompanyHistory table by date desc for this to work.

您可能需要替换 datediff 具有SQL风格的功能，我只知道SQL Server。

You may need to replace datediff with the function for your SQL flavor, I only really know SQL Server.

这篇关于SQL组 - 限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！