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

不带窗口函数的下一行值

sql mysql

hamsy · 技术社区 · 5 年前

我有下面的页面状态更新表。每行都是状态更改:

CREATE TABLE `t1` (
  `page_id` int(11) NOT NULL,
  `country` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`page_id`,`country`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO t1        (page_id, country, status, date) 
VALUES                (1, 'de', 'deactivated', '2018-09-28 14:52:11' ) ,
                      (1, 'de', 'activated', '2018-09-28 14:54:18' ) ,
                      (1, 'de', 'deactivated', '2018-09-28 14:60:12' ) ,
                      (1, 'de', 'moderated', '2018-09-28 14:54:12' ) ,
                      (2, 'es', 'deactivated', '2018-09-28 14:52:01' ) ,
                      (2, 'es', 'activated', '2018-09-28 14:52:07' ) ,
                      (2, 'es', 'deactivated', '2018-09-28 14:52:11' )

我希望以这样的格式查看表格,以便每一行都显示自页面具有特定状态以来的状态:

CREATE TABLE `t2` (
  `page_id` int(11) NOT NULL,
  `country` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_from` datetime DEFAULT NULL,
  `date_to` datetime DEFAULT NULL,
  PRIMARY KEY (`page_id`,`country`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



INSERT INTO t2        (page_id, country, status, date_from, date_to) 
VALUES                (1, 'de', 'deactivated', '2018-09-28 14:52:11', '2018-09-28 14:54:12'  ) ,
                      (1, 'de', 'moderated', '2018-09-28 14:54:12', '2018-09-28 14:54:18'  ) ,
                      (1, 'de', 'activated', '2018-09-28 14:54:18','2018-09-28 14:60:12'  ) ,
                      (1, 'de', 'deactivated', '2018-09-28 14:60:12','2018-01-25 14:60:12' ) ,
                      (2, 'es', 'deactivated', '2018-09-28 14:52:01','2018-09-28 14:52:07'  ) ,
                      (2, 'es', 'activated', '2018-09-28 14:52:07', '2018-09-28 14:52:11' ) ,
                      (2, 'es', 'deactivated', '2018-09-28 14:52:11','2018-01-25 14:52:11'  ) ;

问题是我们仍然在使用MySQL 5.7,还没有升级到带有cte和窗口功能的mysql8,这很容易解决问题:

  SELECT
         country,
         page_id,
         status,
         date as date_from,
        COALESCE(MIN(date) OVER(PARTITION BY country, page_id ORDER BY date DESC
                            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
                        TIMESTAMPADD(day, 1, current_timestamp())) as date_to
  FROM t1 ;

我认为应该有一个技巧与自我加入,但不能弄清楚到底如何!

1 回复 | 直到 5 年前

Gordon Linoff 5 年前

在MySQL 8+中,您通常会使用 LEAD() 为了这个,而不是你复杂的表情。

在早期版本中,可以使用相关子查询:

select t1.*,
       (select tt1.date
        from t1 tt1
        where tt1.country = t1.country and tt1.page_id = t1.page_id and
              tt1.date > t1.date 
        order by tt1.date asc
        limit 1
       ) as date_to
from t1;

NULL 对于 date_to

如果你想重复这个步骤 date_from 相反(我不推荐),您可以:

select t1.*,
       (select min(tt1.date_from, t1.date_from)
        from t1 tt1
        where tt1.page_id = t1.page_id and
              tt1.date_from > t1.date_from
       ) as date_to
from t1;