mysql - SQL 少于 7 天

Question

我需要选择相同 order_ID 的所有值，其中第一个日期和最后一个日期之间的差异等于或小于 7，但我不知道我应该怎么做，可以吗给我一些帮助，我应该怎么做？

数据库布局:

+---------------+---------------------+
| order_ID      | date                |
+---------------+---------------------+
|             1 | 2012-01-21 09:15:00 |
|             1 | 2012-01-21 09:15:00 |
|             2 | 2012-01-21 14:15:00 |
|             3 | 2012-01-22 10:05:00 |
|             3 | 2012-01-22 10:05:00 |
|             4 | 2012-01-22 14:25:00 |
|             5 | 2012-01-25 11:05:00 |
|             6 | 2012-01-27 16:10:00 |
|             6 | 2012-01-27 16:10:00 |
|             7 | 2012-01-27 16:10:00 |
|             8 | 2012-01-28 15:55:00 |
|             9 | 2012-01-28 16:40:00 |
|            10 | 2012-01-28 17:35:00 |
|            10 | 2012-01-28 17:35:00 |
|            10 | 2012-02-03 09:35:00 |
|            12 | 2012-02-03 10:15:00 |
|            13 | 2012-02-05 13:25:00 |
|            14 | 2012-02-05 13:45:00 |
|            15 | 2012-02-05 14:10:00 |
|            15 | 2012-02-05 14:10:00 |
|            16 | 2012-02-07 15:45:00 |
|            17 | 2012-02-11 11:50:00 |
|            18 | 2012-02-11 12:45:00 |
|            19 | 2012-02-14 09:10:00 |
|            19 | 2012-02-16 13:05:00 |
|            19 | 2012-02-16 13:05:00 |
|            20 | 2012-02-17 09:40:00 |
|            21 | 2012-02-19 14:10:00 |
|            21 | NULL                |
|            22 | 2012-02-25 10:20:00 |
|            23 | 2012-02-25 11:05:00 |
|            24 | 2012-02-25 13:35:00 |
|            25 | 2012-02-27 17:10:00 |
|            26 | 2012-03-03 09:40:00 |
|            27 | 2012-03-05 10:45:00 |
|            28 | NULL                |
|            29 | NULL                |
|            29 | NULL                |
|            29 | NULL                |
|            30 | NULL                |
|            30 | NULL                |
+---------------+---------------------+

Answer 1

您需要决定如何处理空值(合并到最大或最小日期值？)，但这应该为您的查询提供一个基本结构。

select order_id
from (
    select order_id, min(date) as mindate, max(date) as maxdate
    from tbl
    group by order_id) t
where datediff(maxdate,mindate) <= 7