mysql - 如何在My SQL中返回某个日期之前的NULL历史记录

Question

Table structure

对于上面的表格，我试图回答以下问题: 对于在 2015 年 1 月 1 日下订单的每个 customer_id，他们的历史(2015 年 1 月 1 日之前)第一个和最后一个订单日期是哪一天？

我的 SQL 查询是:

SELECT customer_id, MIN(order_date) AS first_date, MAX(order_date) AS last_date
FROM Orders
WHERE order_date < '2015-01-01 00:00:00' and customer_id in (SELECT DISTINCT(customer_id) FROM Orders
WHERE order_date between '2015-01-01 00:00:00' and '2015-01-01 23:59:59')
GROUP BY customer_id

但是，这不会返回 2015 年 1 月 1 日之前未下订单的客户的历史记录。我还想返回这些记录，并在日期字段中显示“未找到”之类的内容。谢谢!

Answer 1

您可以在子查询中使用虚拟日期鉴于此

DROP TABLE IF EXISTS O;
CREATE TABLE O (ID INT AUTO_INCREMENT PRIMARY KEY, CUSTOMERID INT, DT DATE);
INSERT INTO O (CUSTOMERID, DT) VALUES
(1,'2015-01-01'),(1,'2014-01-01'),(1,'2014-02-01'),
(2,'2015-01-01'),
(3,'2016-01-01');

这个

select customerid,
         if(MinOrdersBefore2015 = '2050-01-01','null',MinOrdersBefore2015) as MinOrdersBefore2015,
         if(MaxOrdersBefore2015 = '2000-01-01','null',maxOrdersBefore2015) as MaxOrdersBefore2015
from
(
SELECT CUSTOMERID,
         min(CASE WHEN DT < STR_TO_DATE('2015-01-01','%Y-%m-%d') THEN DT ELSE '2050-01-01' end) MinOrdersBefore2015,
         mAX(CASE WHEN DT < STR_TO_DATE('2015-01-01','%Y-%m-%d') THEN DT ELSE '2000-01-01' end) MaxOrdersBefore2015
FROM   O
group by customerid having  sum(case when dt = '2015-01-01' then 1 else 0 end) > 0
) s

结果

+------------+---------------------+---------------------+
| customerid | MinOrdersBefore2015 | MaxOrdersBefore2015 |
+------------+---------------------+---------------------+
|          1 | 2014-01-01          | 2014-02-01          |
|          2 | null                | null                |
+------------+---------------------+---------------------+
2 rows in set (0.00 sec)