Ms Access查询：按CustomerID统计特定Order类型的订单情况

需求回顾 #

你有一张tblOrder表，结构如下：

tblOrder
----------------------------
OrderID ==> Primary Key
CustomerID ==> Number
OrderDate ==> Date
Order ==> Number  -- 存储订单编码（如11、15、17）
Price ==> Currency

需要按CustomerID统计两类订单情况：

• I) 仅下单[Order]=15：客户所有订单的编码都是15，统计这类客户的订单总数；
• ii) 首次下单为[Order]=15，后续又下单了非15的订单：统计这类客户的总订单数，同时显示其[Order]=15的订单数量。

你的示例数据表：

OrderID CustomerID OrderDate Order Price
--------------------------------------------
1       3          1/1/2018  15    100
2       2          3/2/2018  15    300
3       2          7/3/2018  11    400
4       3          2/6/2018  15    200
5       1          5/2/2018  17    300
6       1          11/7/2018 15    600
7       2          1/4/2018  11    200

期望输出：

CustomerID OnlyOrder=15 FirstOrder=15
---------------------------------------
1          Null         Null
2          1            3
3          2            Null

可行查询方案 #

以下是适配Access语法的SQL查询，直接复制到Access查询设计视图的SQL视图中即可使用：

SELECT 
    t.CustomerID,
    IIF(
        -- 条件I：客户所有订单都是15
        COUNT(DISTINCT t.[Order]) = 1 AND MAX(t.[Order]) = 15,
        COUNT(*),
        -- 满足条件ii时，返回客户[Order]=15的订单数
        IIF(
            EXISTS(SELECT 1 FROM tblOrder AS t2 WHERE t2.CustomerID = t.CustomerID AND t2.[Order] = 15)
            AND (SELECT TOP 1 [Order] FROM tblOrder AS t3 WHERE t3.CustomerID = t.CustomerID ORDER BY OrderDate ASC) = 15
            AND COUNT(DISTINCT t.[Order]) > 1,
            SUM(IIF(t.[Order] = 15, 1, 0)),
            Null
        )
    ) AS [OnlyOrder=15],
    IIF(
        -- 条件ii：首次订单是15，且存在非15订单
        (SELECT TOP 1 [Order] FROM tblOrder AS t4 WHERE t4.CustomerID = t.CustomerID ORDER BY OrderDate ASC) = 15
        AND COUNT(DISTINCT t.[Order]) > 1,
        COUNT(*),
        Null
    ) AS [FirstOrder=15]
FROM tblOrder AS t
GROUP BY t.CustomerID
ORDER BY t.CustomerID;

查询逻辑解释 #

1. OnlyOrder=15 字段：

   • 先判断客户是否仅下单15：通过COUNT(DISTINCT [Order])=1确保只有一种订单编码，MAX([Order])=15确认该编码是15，满足则返回总订单数；
   • 若不满足条件I，再判断是否符合条件ii的前置要求（有15订单、首次订单是15、存在其他编码订单），满足则返回该客户15订单的数量；
   • 其余情况返回Null。

2. FirstOrder=15 字段：

   • 通过子查询SELECT TOP 1 [Order] ... ORDER BY OrderDate ASC获取客户的首次订单编码，判断是否为15；
   • 同时通过COUNT(DISTINCT [Order])>1确认客户有非15的订单，满足则返回总订单数，否则返回Null。

注意事项 #

• Order是Access的保留字，必须用方括号[Order]包裹，避免语法错误；
• 确保OrderDate字段的日期格式正确，排序逻辑才能准确获取首次订单；
• Access支持TOP 1、IIF、SUM(IIF(...))这类函数，无需额外配置。

内容的提问来源于stack exchange，提问作者YvetteLee