MySQL Group By 2个相邻的日期

Question

我有一个名为 viewlist 的表，如下所示

     Id            article_id           viewdate
 --------------------------------------------------
     1                1                2015-07-01
     2                1                2015-07-01
     3                1                2015-07-01
     4                2                2015-07-01
     5                2                2015-07-01
     6                1                2015-07-02
     7                2                2015-07-02
     8                1                2015-07-03
     9                2                2015-07-03
    10                1                2015-07-05
    11                1                2015-07-05
 ----------------------------------------------------

我需要编写一个 MySQL 查询来通过对相邻的 viewdate 字段进行分组来获取计数和 article_id

想要的结果如下

 article_id            count                   date_period
-----------------------------------------------------------------
     1                   4                2015-07-01 - 2015-07-02
     2                   3                2015-07-01 - 2015-07-02
     1                   1                2015-07-03 - 2015-07-04
     2                   1                2015-07-03 - 2015-07-04
     1                   2                2015-07-05 - 2015-07-06

是否有任何简单的查询可以得到这种类型的结果？

Answer 1

你可以在这些行上使用一些东西

SELECT v.article_id, COUNT(*), CONCAT(b.dt, ' - ', b.dt2) as 'date_period'
FROM viewlist v INNER JOIN
    (SELECT a.dt, DATE_ADD(a.dt, INTERVAL 1 DAY) AS dt2 FROM (SELECT DISTINCT(viewdate) AS dt FROM viewlist WHERE (DAY(viewdate) MOD 2) = 1) a) b ON v.viewdate BETWEEN b.dt AND b.dt2
GROUP BY 1, 3
ORDER BY b.dt