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

如果您在编写 sql 查询时遇到问题，请尝试将您的需求分解为单个语句。

首先，您需要每个买家的订单数量，您已经解决了这个问题。

SELECT BuyerEMail
, Name
, COUNT(*) as TotalOrders
FROM Orders
WHERE Pay <> 'PayPal'
GROUP BY BuyerEmail, Name
Order By TotalOrders Desc

现在您想要显示每个买家的最新订单。像这样的事情会做:

SELECT BuyerEMail
, Name
, MAX(OrderDate) LatestOrder
FROM Orders 
GROUP BY BuyerEmail, Name

接下来，您需要将输出合并为一条语句。如果比较这两个语句，它们都按同一组(买家和名称)分组，因此您可以将其总结为:

SELECT BuyerEMail
, Name
, COUNT(*) as TotalOrders
, MAX(OrderDate) LatestOrder
FROM Orders
WHERE Pay <> 'PayPal'
GROUP BY BuyerEmail, Name

如果您只想计算具有 Pay != 'PayPal' 的订单，您可以这样做:

SELECT BuyerEMail
, Name
, COUNT(CASE WHEN Pay != 'PayPal' THEN 1 END) as TotalOrders
, MAX(OrderDate) LatestOrder
FROM Orders
GROUP BY BuyerEmail, Name

现在您评论说您还需要最新订单的 OrderID。 sqlserver 2012+ 中的 Lead() 函数可以执行子选择或我更喜欢交叉应用的方式:

SELECT o.*
, OrderID as LastOrderID
, OrderDate as LastOrderDate
FROM (
    SELECT BuyerEMail
    , Name
    , COUNT(*) as TotalOrders
    FROM Orders
    WHERE Pay != 'PayPal'
    GROUP BY BuyerEmail, Name
) o
CROSS APPLY (
    SELECT TOP 1 OrderID, OrderDate
    FROM Orders s
    WHERE s.BuyerEmail = o.BuyerEmail
    ORDER BY OrderDate DESC
) ca

如您所见，如果将其拆分为更小的逻辑部分，事情就会变得更容易。