Home / Questions / 41982
Accepted
Barranka
Asked: 2020-01-03 09:07:07 +0800 CST 2020-01-03 09:07:07 +0800 CST

建立一个包含期间开始和结束日期的表

  • 772

你好！

我有一个具有以下结构的表：

id | fecha_inicio
---+--------------------
10 | 2016-12-01 10:15:00
10 | 2016-12-03 16:02:00
11 | 2016-12-02 11:23:00

我想构建一个具有期间开始和结束的查询，例如，id 的第一个期间的结束日期10是2016-12-03 16:02:00. 我正在寻找的结果如下所示：

id | fecha_inicio        | fecha_final
---+---------------------+--------------------
10 | 2016-12-01 10:15:00 | 2016-12-03 16:02:00
10 | 2016-12-03 16:02:00 | NULL
11 | 2016-12-02 11:23:00 | NULL

有没有办法只使用 SQL 表达式来构建这个查询？我知道可以使用其他语言构建它，但是直接在数据库中构建它会容易得多。

干杯

mysql

2 Answers

  1. Best Answer

    我一直在考虑这个想法，我想我已经找到了解决方案：使用用户变量：

    • 我做的第一件事是通过id升序和fecha降序排序，以便能够“拖动” 的值fecha。
    • 接下来，我对临时变量使用了一个小技巧：由于在查询中为用户变量赋值时，它们会在每一行上更新，因此可以在更新之前引用它们之前的值：
      • 一个表达式case检查id当前记录是否与id前一个记录相同（在变量中@id_ant）：
        • 如果 的值id等于 的值@id_ant，则输入 的值@f_ini（在每一行上用 的值更新fecha）；
        • 如果 的值与 的值不同id ，@id_ant则放一个NULL。
      • 然后，更新 和 的值@id_ant，@f_ini这样就可以在下一行使用它们了。

    结果看起来像这样（我使用 [SQL Fiddle][ http://sqlfiddle.com/#!9/e4522]使问题可重现；我添加了几个值以使示例更清晰）：

    SQL小提琴

    MySQL 5.6 架构设置：

    create table prueba (
    id int unsigned not null,
    fecha datetime
);

insert into prueba values 
(10, '2016-12-01 10:15:00'),
(10, '2016-12-03 16:02:00'),
(10, '2016-12-05 19:19:00'),
(11, '2016-12-02 11:23:00'),
(11, '2016-12-04 22:00:00'),
(12, '2016-12-05 02:00:00');

    查询 1：

    -- Contenido original de la tabla:
select * from prueba;

    结果：

    | id |               fecha |
|----|---------------------|
| 10 | 2016-12-01 10:15:00 |
| 10 | 2016-12-03 16:02:00 |
| 10 | 2016-12-05 19:19:00 |
| 11 | 2016-12-02 11:23:00 |
| 11 | 2016-12-04 22:00:00 |
| 12 | 2016-12-05 02:00:00 |

    查询 2：

    -- Consulta original:
select case when @id_ant = id then @f_ini else null end as f_fin
     -- Actualiza el Id y la fecha de inicio
     , @id_ant := id as id
     , @f_ini := fecha as f_ini
from (select @id_ant := 0, @f_ini := null, @f_fin := null) as init, prueba
order by id asc, fecha desc;

    结果：

    |               f_fin | id |               f_ini |
|---------------------|----|---------------------|
|              (null) | 10 | 2016-12-05 19:19:00 |
| 2016-12-05 19:19:00 | 10 | 2016-12-03 16:02:00 |
| 2016-12-03 16:02:00 | 10 | 2016-12-01 10:15:00 |
|              (null) | 11 | 2016-12-04 22:00:00 |
| 2016-12-04 22:00:00 | 11 | 2016-12-02 11:23:00 |
|              (null) | 12 | 2016-12-05 02:00:00 |

    查询 3：

    -- Consulta con los campos y los valores en el orden correcto:
select id, f_ini, f_fin
from (
        select case when @id_ant = id then @f_ini else null end as f_fin
             -- Actualiza el Id y la fecha de inicio
             , @id_ant := id as id
             , @f_ini := fecha as f_ini
        from (select @id_ant := 0, @f_ini := null, @f_fin := null) as init, prueba
        order by id asc, fecha desc
    ) as a
order by id, f_ini

    结果：

    | id |               f_ini |               f_fin |
|----|---------------------|---------------------|
| 10 | 2016-12-01 10:15:00 | 2016-12-03 16:02:00 |
| 10 | 2016-12-03 16:02:00 | 2016-12-05 19:19:00 |
| 10 | 2016-12-05 19:19:00 |              (null) |
| 11 | 2016-12-02 11:23:00 | 2016-12-04 22:00:00 |
| 11 | 2016-12-04 22:00:00 |              (null) |
| 12 | 2016-12-05 02:00:00 |              (null) |
    • 2

  2. 也可以MIN()在子查询中使用：

    select t.id,
       t.fecha_inicio,
       (select min(t2.fecha_inicio)
          from tbl t2
         where t2.id = t.id
           and t2.fecha_inicio > t.fecha_inicio) as fecha_final
  from tbl t
 order by t.id, t.fecha_inicio;

    Demo

    Demo 2

    • 2