mysql中的串联范围描述

用途:

     SELECT a, GROUP_CONCAT(x.island)
         FROM (SELECT y.a,
                               CASE
                                  WHEN MIN(y.b) = MAX(y.b) THEN
                                    CAST(MIN(y.b) AS VARCHAR(10))
                                  ELSE
                                    CONCAT(MIN(y.b), '-', MAX(y.b))
                               END AS island
                      FROM (SELECT t.a, t.b,
                                            CASE
                                                WHEN @prev_b = t.b -1 THEN
                                                    @group_rank
                                                ELSE
                                                  @group_rank := @group_rank + 1
                                            END AS blah,
                                            @prev_b := t.b
                                  FROM TABLE t
                                  JOIN (SELECT @group_rank := 1, @prev_b := 0) r
                           ORDER BY t.a, t.b) y
               GROUP BY y.a, y.blah) x  
GROUP BY a

其思想是,如果为组顺序值分配一个值,那么可以使用最小值/最大值来获得适当的值。IE:

a  |  b | blah
---------------
a  |  1 | 1
a  |  2 | 1
a  |  4 | 2
a  |  5 | 2

我还发现马丁·史密斯对另一个问题的回答很有帮助:

printing restaurant opening hours from a database table in human readable format using php