MySQL 查询(客户 ID 和第一次、第二次和第三次购买的日期)

Question

我正在尝试创建一个仪表板，其中包含客户 ID 以及第一次、第二次和第三次购买的日期。我使用MySQL、Northwind db。

我的查询对于第一次购买完全正常，但我不明白如何查找每个客户的第二次和第三次购买日期。

现在我正在尝试下一步:2nd_purchase_date 是 1st_purchase_date 之后的下一个 MIN(OrderDate)，但我收到以下错误“无效使用组功能”

DROP TABLE IF EXISTS t1;
CREATE TABLE t1
(
    CustomerID varchar(5),
    OrderDate datetime,
    OrderID int,
    i int
);

INSERT INTO t1(CustomerID, OrderDate, OrderID, i)
SELECT CustomerID, min(OrderDate), min(OrderID),1
FROM Orders
GROUP BY CustomerID;

INSERT INTO t1(CustomerID, OrderDate, OrderID, i)
SELECT CustomerID,min(OrderDate), min(OrderID),2
FROM Orders
WHERE min(OrderDate) 
NOT IN 
(
SELECT CustomerID, min(OrderDate), min(OrderID)
FROM Orders
)
GROUP BY CustomerID;

INSERT INTO t1(CustomerID, OrderDate, OrderID, i)
SELECT CustomerID,min(OrderDate), OrderID,3
FROM Orders
WHERE min(OrderDate) 
NOT IN 
(
SELECT CustomerID, min(OrderDate), min(OrderID) FROM Orders
)
GROUP BY CustomerID;

Answer 1

如上例所示here ，您可以将LIMIT offset, count功能与ORDER BY column ASC结合使用来选择单个用户的第二个购买日期的条目(ID为XXX 替换为相应的值):

SELECT CustomerID, OrderDate, OrderID
  FROM Orders
  WHERE CustomerID = XXX
  ORDER BY OrderDate ASC
  LIMIT 1, 1;

或第三个购买日期的条目:

SELECT CustomerID, OrderDate, OrderID
  FROM Orders
  WHERE CustomerID = XXX
  ORDER BY OrderDate ASC
  LIMIT 2, 1;

...等等。

<小时/>

您通过以下方式获得第一个条目

SELECT CustomerID, OrderDate, OrderID
  FROM Orders
  WHERE CustomerID = XXX
  ORDER BY OrderDate ASC
  LIMIT 0, 1;

或同等内容:

SELECT CustomerID, OrderDate, OrderID
  FROM Orders
  WHERE CustomerID = XXX
  ORDER BY OrderDate ASC
  LIMIT 1;

<小时/>

这应该为您提供单个客户的前三个订单:

SELECT CustomerID, OrderDate, OrderID
  FROM Orders
  WHERE CustomerID = XXX
  ORDER BY OrderDate ASC
  LIMIT 3;

虽然从性能角度来看，以下内容绝对不好，但它仍然可以为您提供每个客户的前三个订单:

SELECT o1.CustomerID, o1.OrderDate, o1.OrderID
  FROM Orders o1
  WHERE o1.OrderID IN
        (SELECT o2.OrderID
           FROM Orders o2
           WHERE o1.CustomerID = o2.CustomerID
           ORDER BY o2.OrderDate ASC LIMIT 3)
  ORDER BY o1.CustomerID, o2.OrderDate ASC;