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)。您需要CompanyHistory
按日期desc对表进行排序才能使其正常工作。