我有一张“订单”表,看起来像这样:
+---------------+--------------+------------+
| 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
is 2018-01-19最后一行的值为空
next_date我怎样才能得到以天为单位的日期差异? 我知道这是因为我已经按订单号和
>=` 但我不知道还有什么办法。我觉得有一个显而易见的简单的解决办法,就是逃避我。有什么帮助吗?
sql小提琴
如果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')
;
3条答案
按热度按时间dtcbnfnu1#
你可以这样做。但是你的
order 4
有一个previous date
比order 3
. 所以它会产生一个负值。gtlvzcf82#
如果您对基于mysql 8.x或更高版本的非row\u number()解决方案感兴趣,请查看下一个解释。
说明:
1) 首先,我们从orders表中选择所有日期,通过升序对它们进行排序,并为每个日期分配一个虚拟的自动递增et id。我们会得到这样的结果:
2) 我们创建了一个与前一个类似的查询,但这次我们放弃了第一行,如下所示:
这里唯一的问题是,我们必须将限制数字硬编码为足够高的数字,这样我们可以确保我们将选择除第一行以外的所有行。
3) 此时,您应该考虑通过虚拟生成的id连接前面的两个结果。因此,让我们看看最终的查询:
您可以在这里看到工作示例:http://sqlfiddle.com/#!1572ea/27年9月
yk9xbfzb3#
订单号和日期之间没有直接关系。订单号越高,日期越短。
所以,我们不能用订单号来决定下一个订单日期。
由于您的mysql版本是8.0,我们可以对
Row_Number()
功能。我们将使用
orders
表两次放入两个不同的派生表中,行号是根据表的升序指定的date
. 两者之间的区别在于其中一个表将修改行号(递增1)。现在,我们需要做的就是根据行号值在它们之间进行内部连接。由于行号之间的“滑动间隔”,我们将得到“当前”和“下一个”日期。内部连接还将确保没有匹配“next”日期的最后一个“current”日期行不会出现。
最终,我们可以使用
DateDiff()
函数来确定日期之间的差异。请尝试以下操作:
db小提琴演示