Mysql - 行之间的条件

Question

我查看了所有的线程，但找不到任何东西来回答我的问题(或者我没有搜索正确的问题:P)我正在自学mysql，现在我陷入了困境，找不到任何解决方案。

所以我的问题是，是否可以从 2 个日期之间的表中选择行，并且仅选择行之间的差异例如 6 个月的行，如果不显示已经过去了多少时间。

表:

+----+--------+--------------+------------+
| id |  name  |    action    |    date    |
+----+--------+--------------+------------+
| 1  | name 1 |   exchange   | 2011-06-15 |
| 2  | name 1 |   exchange   | 2011-12-15 |
| 3  | name 1 |   exchange   | 2012-06-15 |
| 4  | name 1 |   exchange   | 2013-01-15 | -1 month
| 5  | name 2 |   exchange   | 2014-01-15 |
| 6  | name 2 | intervention | 2014-05-15 |
| 7  | name 2 |   exchange   | 2014-06-15 |
| 8  | name 2 |   exchange   | 2015-05-15 | - 11 months
+----+--------+--------------+------------+

所以我陷入困境的是

SELECT * 
FROM (select * from table
        WHERE intervention like '%exchange%'
        AND date between '2011-01-15' and NOW() )
WHERE ....
 - if difference between row 1 and row 2, row 2 and row 3 till no rows? else write difference ?

但是根据我的猜测，我必须进行 2 个选择，然后加入它们或类似的东西，1 个选择将采用所有“常规”行，而一个选择将在结果之间添加条件，我只是不知道我到底是怎样的应该这样做，或者如果仅使用 sql 就可以。

你们有解决办法吗？如果你这样做，你能解释一下你做了什么吗？ :D

期望的输出:

+--------+--------------+------------+
|  name  |    action    |    date    |
+--------+--------------+------------+
| name 1 |   exchange   | 2011-06-15 |
| name 1 |   exchange   | 2011-12-15 |
| name 1 | OK exchange  | 2011-12-15 | - generated because row 1 and 2 meet the requirements
| name 1 |   exchange   | 2012-06-15 |
| name 1 | OK exchange  | 2012-06-15 | - generated because row 2 and 3 meet the requirements
| name 1 |   exchange   | 2013-01-15 | 
| name 1 | 1 month late | 2013-12-15 | - generated because row 3 and 4 DONT meet the requirements

提前谢谢您。恕我直言，iaiu

Answer 1

在我看来，您想要一个混合了源数据和处理数据的结果，这有点......非正统。让我们从头开始尝试吧。首先，我们将表与它本身连接起来，给它们两个昵称。请注意，t2 将接下来的每一行与 t1 配对:

select t1.name as name, t1.date as date, datediff(t2.date, t1.date) as diff_in_days
from table t1, table t2
where
  t1.action like '%exchange%' and
  t1.date between '2011-01-15' and NOW() and
  t1.name = t2.name and
  t1.id = t2.id-1
order by t1.name, t1.date;

我们得到的配对之间存在时间差，但以天为单位。 “6 个月”的定义由您决定，现在假设是 182 天。现在让我们将上述内容包装到您的业务逻辑中:

select
  name,
  date,
  if(diff_in_days <=182, "OK", concat(diff_in_days - 182, "days late")) as message
from (
select t1.name as name, t1.date as date, datediff(t2.date, t1.date) as diff_in_days
from table t1, table t2
where
  t1.action like '%exchange%' and
  t1.date between '2011-01-15' and NOW() and
  t1.name = t2.name and
  t1.id = t2.id-1
) res
order by res.name, res.date;

现在，如果您还需要原始数据，请使用 UNION并将上述表格和原始表格的行并排放置。