SQL 最近的命令？微软SQL

我只是写一个查询来查看我的客户的客户数据库并列出他们下的订单数量等。

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

有任何想法吗？

这是我的查询

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

最佳答案

试一试；

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

关于SQL 最近的命令？微软SQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37324365/