MySQL - 如果订单来自新客户，则向表添加列并插入 "tag"

Question

我有简单的表格:

Order_ID  Client_ID   Date         Order_Status
1         1           01/01/2015   3
2         2           05/01/2015   3
3         1           06/01/2015   3
4         2           10/01/2015   3
5         1           12/01/2015   4
6         1           05/02/2015   3

我想识别来自新客户的订单，这些订单是该客户在 Order_Status = 3 的情况下首次下单的同一个月的订单

因此输出表应如下所示:

Order_ID  Client_ID   Date         Order_Status  Order_from_new_customer
1         1           01/01/2015   3             yes
2         2           05/01/2015   3             yes
3         1           06/01/2015   3             yes
4         2           10/01/2015   3             yes
5         1           12/01/2015   4             NULL
6         1           05/02/2015   3             no

我无法成功找出查询。非常感谢您的帮助。

Answer 1

连接一个子查询，获取每个客户的第一个订单的日期。

SELECT o.*, IF(MONTH(o.date) = MONTH(f.date) AND YEAR(o.date) = YEAR(f.date),
               'yes', 'no') AS order_from_new_customer
FROM orders AS o
JOIN (SELECT Client_ID, MIN(date) AS date
      FROM orders
      WHERE Order_Status = 3
      GROUP BY Client_ID) AS f
ON o.Client_ID = f.Client_ID