mysql - 如果顺序中断，如何将日期范围显示为连续日期的最小日期和最大日期以及不同的范围

Question

假设一个表有考生的考试通过日期，现在我想显示考生通过考试的开始日期到结束日期的范围

表1

Person.              year
11.                    1-1- 2013
11.                     1-1-2014
11.                     1-1-2015
11.                     1-1-2017
11.                     1-1-2018
11.                     1-1-2020

现在我想整理一下顺序，这样展示

11                    1-1-2013  1-1-2015
11.                   1-1-2017  1-1-2018
11.                   1-1-2020

如何在sql中做到这一点

Answer 1

您可以使用这样的查询来获得您想要的结果，但这不是最好的代码:

SELECT person, 
    CONCAT (
        MIN( myyear),
         IF( MIN(myyear) = MAX(myyear) ,'', CONCAT('    ',MAX(myyear)) )
    )  AS `year`
FROM (
    SELECT
        e.*
        , @grp := if(YEAR(@last_year)+1 = YEAR(e.myyear), @grp, @grp+1) as grp
        , @last_year := e.myyear as tmp
    FROM exam e
    CROSS JOIN ( SELECT @grp :=0, @ast_year:=0) AS init
    ORDER BY person,myyear
    ) as result
group by grp;


**sample table**

mysql> SELECT * FROM exam;
+----+--------+------------+
| id | person | myyear     |
+----+--------+------------+
|  1 |     11 | 2013-01-01 |
|  2 |     11 | 2014-01-01 |
|  3 |     11 | 2015-01-01 |
|  4 |     11 | 2017-01-01 |
|  5 |     11 | 2018-01-01 |
|  6 |     11 | 2020-01-01 |
+----+--------+------------+
6 rows in set (0,00 sec)

示例查询

mysql> SELECT person,
    ->     CONCAT (
    ->         MIN( myyear),
    ->          IF( MIN(myyear) = MAX(myyear) ,'', CONCAT('    ',MAX(myyear)) )
    ->     )  AS `year`
    -> FROM (
    ->     SELECT
    ->         e.*
    ->         , @grp := if(YEAR(@last_year)+1 = YEAR(e.myyear), @grp, @grp+1) as grp
    ->         , @last_year := e.myyear as tmp
    ->     FROM exam e
    ->     CROSS JOIN ( SELECT @grp :=0, @ast_year:=0) AS init
    ->     ORDER BY person,myyear
    ->     ) as result
    -> group by grp;
+--------+--------------------------+
| person | year                     |
+--------+--------------------------+
|     11 | 2013-01-01    2015-01-01 |
|     11 | 2017-01-01    2018-01-01 |
|     11 | 2020-01-01               |
+--------+--------------------------+
3 rows in set (0,00 sec)

mysql>