MYSQL 我需要从结果集中删除旧记录

Question

我几乎已经明白了，但还没有完全明白。我正在返回我需要的数据，但我只想输出每个memberID 的最新 paymentDate。

我的查询如下:

SELECT a.mID, a.ageGroup,a.dayAvail,a.startTime,b.memberType,b.paymentDate,b.id FROM referrals a
GROUP BY b.id
LEFT JOIN membership b ON a.mID=b.memberID
WHERE a.ageGroup='Young Adult'
ORDER BY b.memberID,b.paymentDate DESC

我返回的结果是:

memberID    ageGroup      dayAvail  startTime   memberType    paymentDate    b.id
226      Young Adult    Wednesday    6:30 PM    Associate      8/31/2013     869
226      Young Adult    Wednesday    6:30 PM    Associate1     14/30/1993    158
1262    Young Adult    Thursday    7:00 PM    Clinical        7/1/2013      762
1262    Young Adult    Thursday    7:00 PM    Clinical        3/23/2010     610
1271    Young Adult    Tuesday    5:30 PM    Clinical         8/1/2013      687
1271    Young Adult    Tuesday    5:30 PM    Associate1      16/18/2010     619

根据每个memberID的 paymentDATE，我只想返回最新的 paymentDate (或者我猜，每个memberID的最后一个b.id)

谢谢。

Answer 1

您应该使用 a.mID 而不是 b.id 进行GROUP BY。请尝试使用以下查询

SELECT a.mID, a.ageGroup,a.dayAvail,a.startTime,b.memberType,MAX(b.paymentDate),b.id 
FROM referrals a    
LEFT JOIN membership b ON a.mID=b.memberID
GROUP BY a.mID
WHERE a.ageGroup='Young Adult'
ORDER BY a.mID