代码之家 › 专栏 › 技术社区 › learner

基于日期间隔生成结果

sql mysql

learner · 技术社区 · 5 年前

我正在处理一个SQL查询,其中有两个表名为Theatre,Movies like this:

剧场

Theatre with columns theatre id and show date:

id      show_date
------------------
1       2018-05-01
2       2018-05-01
1       2018-05-03
3       2018-05-04
2       2018-05-14
3       2018-05-11
2       2018-05-14

电影

Movie with columns movie id and movie name:

id  name
----------
1   Avatar
2   Spiderman
3   Avengers

预期的结果是:(我为每一行添加了注释作为解释)

theatre_id  show_date   movie
---------------------------------------------

1           2018-05-01  Avatar  /* 1st theatre, 1st date occurrence so picking 1st movie
2           2018-05-01  Avatar  /* 2nd theatre, 1st date occurrence so picking 1st movie */
1           2018-05-03  Avatar  /* 1st theatre, with 1 day gap (1st may 2018 to 3rd may 2018), so picking 1st movie
3           2018-05-04  Avatar  /* 3rd theatre, 1st date occurrence so picking 1st movie */
2           2018-05-10  Spiderman /* 2nd theatre, with 8 days gap (1st may 2018 to 10th may 2018), so picking 2nd movie */
3           2018-05-11  Spiderman /* 3rd theatre, with 6 days gap (4th may 2018 to 11th may 2018) so picking 2nd movie */
2           2018-05-14  Avengers /* 2nd theatre, with 3 days gap (10th may 2018 to 14th may 2018), so picking 3rd movie */

drop table if exists theatre;

CREATE TABLE theatre ( 
  id INTEGER NOT NULL,
  show_date date NOT NULL
);

drop table if exists movie;

CREATE TABLE movie ( 
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL
);


insert into movie values (null, 'Avatar');
insert into movie values (null, 'Spiderman');
insert into movie values (null, 'Avengers');

insert into theatre values( 1, cast('2018-05-01' as date));
insert into theatre values( 2, cast('2018-05-01' as date));
insert into theatre values( 1, cast('2018-05-03' as date));
insert into theatre values( 3, cast('2018-05-04' as date));
insert into theatre values( 2, cast('2018-05-10' as date));
insert into theatre values( 3, cast('2018-05-11' as date));
insert into theatre values( 2, cast('2018-05-14' as date));

0 回复 | 直到 5 年前

GMB 5 年前

这有点复杂;下面是一种使用MySQL 8.0中提供的窗口函数的方法。

我理解你的问题,你需要把 theatre 在以后分配给同一部电影的组中。为此,您可以使用 lag() 检索上一个 show_date movie 表格并为每组分配一部电影:

select t.id, t.show_date, m.name movie
from (
    select 
        t.*, 
        sum(case when show_date <= lag_show_date + interval 2 day then 0 else 1 end) 
            over(partition by id order by show_date) grp
    from (
        select 
            t.*, 
            lag(show_date) over(partition by id order by show_date) lag_show_date
        from theatre t
    ) t
) t
inner join (
    select m.*, row_number() over(order by id) grp from movie m
) m
    on m.grp = t.grp