最近的SQL订单？MS SQL

Question

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

我在这个查询中遇到的困难是如何只显示该电子邮件的最近OrderID。

有什么想法吗？

以下是我的查询：

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

- Budyn

---

取决于您如何定义“最近的订单”。您的表格中有日期吗？ - Vamsi Prabhala

是的，我们有OrderDate列。我也在考虑使用FirstOrDefault，但在SQL中无法这样做... - Budyn

你有一个买家列表的表格吗？ - Joe Taras

在 SQL Server 中搜索“每组最大的 n”（greatest n per group and sql server），在您的情况下，n 为 1。链接 - Conrad Frix

@Pred 如果您只需要一个值（例如orderid），那么First_Value就可以了，但通常人们希望获取整个记录。 - Conrad Frix

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

如果您只想计算付款方式不为“PayPal”的订单数量，可以执行以下操作：

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

现在你提到你还想获取最新订单的订单ID。在SQLServer 2012+中，可以使用Lead()函数、子查询或者我更喜欢使用Cross Apply：

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

正如您所看到的，如果将其分解为更小的逻辑部分，则事情变得更加容易。

- Raul

Answer 2

尝试一下这个；

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

- Rich Benner

---

这个可以工作，但是我想显示与之相关的OrderID而不是OrderDate。我尝试将其添加到JOIN中，但出现错误...我需要一个单独的Join来添加最新日期的OrderID吗？ - Budyn

你可以这样做，或者你可以嵌套另一个子查询。我已经更新了我的答案，并加入了另一个连接到订单表（o2）以获取此信息。如果您在订单日期中有一个日期时间字段，则此方法效果最佳。 - Rich Benner

这个问题你解决了吗？ - Rich Benner

它可以工作，谢谢 :) 抱歉回复晚了，我在重命名列 :) - Budyn

Answer 3

如果您的表有日期或日期时间字段，您可以按照此字段进行降序排序，以便查看最近的订单。否则，如果您的表中没有任何日期字段，但是您有一个递增的ID和您的字段正在增加并且仅有插入，则也可以将主键用作desc。

我强烈建议在您的表格中使用数据字段，如果您没有任何人。

- Diogo de Góes Zanetti

Answer 4

试试这个：

SELECT 
     BuyerEMail
     ,COUNT(*) HowMany
     ,Name
FROM Orders O
     JOIN (Select Max(OrderDate) LastOrder, BuyerEmail From Orders Group By BuyerEmail) R On R.BuyerEmail = O.BuyerEmail
WHERE 
     Pay != 'PayPal'
GROUP BY 
     BuyerEmail
     ,Name
ORDER BY 
     HowMany Desc

- Joe C

Answer 5

如果我理解正确，可能是这样的吗？

;with cte as (
    select BuyerEMail, lastOrderId = Max(OrderId) 
    from Orders
    group by BuyerEMail
),
cte1 as (
    select BuyerEMail, ordersCount = count(*) 
    from orders 
    where ( pay != 'paypal' ) 
    group By BuyerEMail
)
select
    a.lastOrderId, a.BuyerEMail, b.ordersCount
from
    cte as a
    inner join cte1 as b on ( a.BuyerEMail = b.BuyerEMail )

- shadow