GROUP BY子句的TOP N问题 [英] TOP N problem with GROUP BY clause

问题描述

问题：我需要找到所有有效的[GiftPledges]，其中最后三个[GiftDetails]的金额为零。

  SELECT gp.PledgeId FROM GiftPledge gp 
 INNER JOIN GiftDetail gd ON gp.PledgeId = gd.PledgeId 
 WHERE gp.PledgeStatus ='A'
 GROUP BY PledgeId 
 HAVING COUNT （PledgeId）> = 3 
  

现在，我的所有[GiftPledges] [GiftDetails]。

  SELECT TOP 3 gdi.Amt FROM GiftDetail gdi 
 INNER JOIN GiftHeader ghi ON gdi.GiftRef = ghi.GiftRef 
 WHERE gdi.PledgeId = gp.PledgeId 
 ORDER BY ghi.GDate DESC 
  

这给了我最近的三个[GiftDetails]与给定的[GiftPledge]相关联。问题是我不知道如何总结第二个查询，并将它作为第一个查询中的WHERE子句的一部分。

我发现本文关于Top n每组，而且似乎像我需要领导的方向，但我不确定我是否在正确的轨道上。

任何帮助，澄清或建议将不胜感激。

解决方案

  SELECT gp。 PledgeId FROM GiftPledge gp 
 INNER JOIN GiftDetail gd ON gp.PledgeId = gd.PledgeId 
 WHERE gp.PledgeStatus ='A'
 GROUP BY PledgeId 
 HAVING COUNT（PledgeId）> = 3 
 AND 
 GP.PledgeID in（
 SELECT PledgeID from 
（
 SELECT TOP 3 gp.PledgeID，gdi.Amt FROM GiftDetail gdi 
 INNER加入GiftHeader ghi ON gdi.GiftRef = ghi.GiftRef 
 WHERE g di.PledgeId = gp.PledgeId 
 ORDER BY ghi.GDate DESC 
）x_amt 
 Group By PledgeID 
拥有SUM（AMT））x_sum = 0 
  

总之就是这样。

The problem: I need to find all active [GiftPledges] that have the last three [GiftDetails] have a zero amount.

SELECT gp.PledgeId FROM GiftPledge gp
      INNER JOIN GiftDetail gd ON gp.PledgeId = gd.PledgeId
      WHERE gp.PledgeStatus = 'A'
      GROUP BY PledgeId
      HAVING COUNT(PledgeId) >= 3

Now, I have all my [GiftPledges] that have at least three [GiftDetails].

SELECT TOP 3 gdi.Amt FROM GiftDetail gdi
      INNER JOIN GiftHeader ghi ON gdi.GiftRef = ghi.GiftRef
      WHERE gdi.PledgeId = gp.PledgeId
      ORDER BY ghi.GDate DESC

This gives me the three most recent [GiftDetails] associated with a given [GiftPledge]. The problem is that I don't know how to sum the second query and have it be a part of the WHERE clause in the first query.

I found this article about "Top n per Group" and that seems like the direction I need to be headed, but I'm not sure I'm on the right track.

Any help, clarifications or suggestions would be greatly appreciated.

解决方案

SELECT gp.PledgeId FROM GiftPledge gp
      INNER JOIN GiftDetail gd ON gp.PledgeId = gd.PledgeId
      WHERE gp.PledgeStatus = 'A'
      GROUP BY PledgeId
      HAVING COUNT(PledgeId) >= 3 
AND
GP.PledgeID in (
SELECT PledgeID From
(
SELECT TOP 3 gp.PledgeID, gdi.Amt  FROM GiftDetail gdi
      INNER JOIN GiftHeader ghi ON gdi.GiftRef = ghi.GiftRef
      WHERE gdi.PledgeId = gp.PledgeId
      ORDER BY ghi.GDate DESC
) x_amt 
Group By PledgeID
Having SUM(AMT) ) x_sum = 0

something like that anyway.

这篇关于GROUP BY子句的TOP N问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！