快去吧
SELECt TOP 1000 o.BuyerEMail,COUNT(*) HowMany,o.Name,o2.OrderIDFROM Orders oJOIN ( SELECt BuyerEmail ,MAX(OrderDate) Latest FROM Orders GROUP BY BuyerEmail ) lON o.BuyerEmail = l.BuyerEmailJOIN Orders o2ON l.BuyerEmail = o2.BuyerEmailAND l.OrderDate = o2.OrderDateWHERe Pay != 'PayPal'GROUP BY o.BuyerEmail ,o.Name ,l.LatestORDER BY COUNT(*) DESC
它通过子查询中的每个电子邮件地址计算出最新的顺序，然后您可以在SELECt中使用它。我还对表进行了别名处理，以使事情变得更容易。
您也可以通过嵌套子查询来另一种方式。
SELECT TOP 1000 o.BuyerEMail,COUNT(*) HowMany,o.Name,o2.OrderIDFROM Orders oJOIN ( SELECt BuyerEmail ,OrderID FROM Orders ord JOIN ( SELECt BuyerEmail ,MAX(OrderDate) Latest FROM Orders GROUP BY BuyerEmail ) ma ON ord.BuyerEmail = ma.BuyerEmail AND ord.OrderDate = ma.OrderDate ) lON o.BuyerEmail = l.BuyerEmailWHERe Pay != 'PayPal'GROUP BY o.BuyerEmail ,o.Name ,l.LatestORDER BY COUNT(*) DESC
