SQL最新命令？微软SQL

Question

我只是写一个查询来查看我的客户客户数据库，并列出他们已经下了多少个订单等。

我正在努力添加到该查询中的是仅向我显示该电子邮件的最新OrderID

有任何想法吗？

这是我的查询

select top 1000 
     BuyerEMail
     ,COUNT(*) HowMany
     ,Name
from Orders 
where 
     Pay != 'PayPal'
group by 
     BuyerEmail
     ,Name
order by 
     HowMany Desc

Answer 1

快去吧

SELECT TOP 1000 
o.BuyerEMail
,COUNT(*) HowMany
,o.Name
,o2.OrderID
FROM Orders o
JOIN 
    (
     SELECT 
     BuyerEmail
     ,MAX(OrderDate) Latest 
     FROM Orders 
     GROUP BY BuyerEmail
    ) l
ON o.BuyerEmail = l.BuyerEmail

JOIN Orders o2
ON l.BuyerEmail = o2.BuyerEmail
AND l.OrderDate = o2.OrderDate

WHERE Pay != 'PayPal'

GROUP BY 
    o.BuyerEmail
    ,o.Name
    ,l.Latest
ORDER BY 
    COUNT(*) DESC

它通过子查询中的每个电子邮件地址计算出最新的顺序，然后您可以在SELECT中使用它。我还对表进行了别名处理，以使事情变得更容易。

您也可以通过嵌套子查询来另一种方式。

SELECT TOP 1000 
o.BuyerEMail
,COUNT(*) HowMany
,o.Name
,o2.OrderID
FROM Orders o
JOIN 
    (
     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
    ) l
ON o.BuyerEmail = l.BuyerEmail

WHERE Pay != 'PayPal'

GROUP BY 
    o.BuyerEmail
    ,o.Name
    ,l.Latest
ORDER BY 
    COUNT(*) DESC