我有一个表“订单”,看起来像这样：

+---------------+--------------+------------+
| customer_name | order_number |    date    |
+---------------+--------------+------------+
| jack          |            1 | 2018-01-01 |
| jack          |            2 | 2018-01-06 |
| jack          |            3 | 2018-01-19 |
| jack          |            4 | 2018-01-06 |
| jack          |            5 | 2018-02-27 |
| jack          |            6 | 2018-02-02 |
+---------------+--------------+------------+

现在,我想要一个表,该表可以显示连续日期(以天为单位)之间的差异.像这样：

+------------+------------+------+
|    date    | next_date  | diff |
+------------+------------+------+
| 2018-01-01 | 2018-01-06 |    5 |
| 2018-01-06 | 2018-01-06 |    0 |
| 2018-01-06 | 2018-01-19 |   13 |
| 2018-01-19 | 2018-02-02 |   14 |
| 2018-02-02 | 2018-02-27 |   25 |
+------------+------------+------+

我使用的查询是这样的：

SELECT orders.date, MIN(table1.date) FROM orders
    LEFT JOIN orders table1
    on orders.customer_name = table1.customer_name
    AND table1.date >= orders.date
    AND table1.order_number !=  orders.order_number
    WHERE orders.customer_name = 'jack'
    GROUP BY orders.order_number, orders.date
    ORDER BY orders.date;

这是输出：

+------------+------------+
|    date    | next_date  |
+------------+------------+
| 2018-01-01 | 2018-01-06 |
| 2018-01-06 | 2018-01-06 |
| 2018-01-06 | 2018-01-06 |
| 2018-01-19 | 2018-02-02 |
| 2018-02-02 | 2018-02-27 |
| 2018-02-27 |    NULL    |
+------------+------------+

如您所见,这里有一些问题.

>有两行,其中date和next_date均为2018-01-06.
> next_date是2018-01-19的行没有出现`
>最后一行的next_date为NULL
>如何获得以天为单位的日期差异？

我知道这是因为我已按order_number和> =分组
但我不知道该如何处理.我觉得有一个显而易见的简单解决方案正在逃避我.有什么帮助吗？

SQL Fiddle

如果SQL Fiddle不起作用：

CREATE TABLE orders
    (`customer_name` varchar(4), `order_number` int, `date` varchar(10))
;

INSERT INTO orders
    (`customer_name`, `order_number`, `date`)
VALUES
    ('jack', 1, '2018-01-01'),
    ('jack', 2, '2018-01-06'),
    ('jack', 3, '2018-01-19'),
    ('jack', 4, '2018-01-06'),
    ('jack', 5, '2018-02-27'),
    ('jack', 6, '2018-02-02')
;

解决方法:

如果您对非ROW_NUMBER()解决方案(取决于MySQL 8.x或更高版本)感兴趣,请查看以下说明.

说明：

1)首先,我们从订单表中选择所有日期,通过升序对每个日期进行排序并为每个日期分配一个虚拟的自动增量ID.我们将得到如下内容：

SELECT (@row_number := @row_number + 1) AS orderNum, date 
FROM ORDERS, (SELECT @row_number:=0) AS t
ORDER BY date;

Output:
1   2018-01-01
2   2018-01-06
3   2018-01-06
4   2018-01-19
5   2018-02-02
6   2018-02-27

2)我们创建与上一个查询类似的查询,但是这次我们丢弃第一行,如下所示：

SELECT (@row_number2 := @row_number2 + 1) AS orderNum, date
FROM ORDERS, (SELECT @row_number2 := 0) AS t
ORDER BY date
LIMIT 999999999999
OFFSET 1;

Output:
1   2018-01-06
2   2018-01-06
3   2018-01-19
4   2018-02-02
5   2018-02-27

这里唯一的问题是,我们必须将LIMIT号硬编码为足够高的数字,因此我们可以确保选择除第一个行以外的所有行.

3)此时,您应该考虑通过虚拟生成的ID将之前的两个结果结合在一起.因此,让我们看一下最终查询：

SELECT
    startDate.date AS date,
    nextDate.date AS next_date,
    DATEDIFF(nextDate.date, startDate.date) AS diff
FROM
    (SELECT (@row_number := @row_number + 1) AS orderNum, date 
     FROM ORDERS, (SELECT @row_number:=0) AS t
     ORDER BY date) AS startDate
INNER JOIN
    (SELECT (@row_number2 := @row_number2 + 1) AS orderNum, date
     FROM ORDERS, (SELECT @row_number2 := 0) AS t
     ORDER BY date
     LIMIT 999999999999
     OFFSET 1) AS nextDate ON nextDate.orderNum = startDate.orderNum;

Output:
2018-01-01  2018-01-06  5
2018-01-06  2018-01-06  0
2018-01-06  2018-01-19  13
2018-01-19  2018-02-02  14
2018-02-02  2018-02-27  25

您可以在这里看到工作示例：http://sqlfiddle.com/#!9/1572ea/27