【Question】

Can I group values of a column into multiple range definitions?

Here's an example table: mysql> select * from t;

+------+------+

| x | y |

+------+------+

| 1 | 1 |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 2 |

| 6 | 2 |

| 7 | 1 |

| 8 | 1 |

| 9 | 1 |

+------+------+

I want to select the following info: y=1 has ranges of x: 1-3, 7-9, y=2 has ranges: 4-6.

Definition and data:

create table t (x int, y int);

insert into t(x,y) values (1,1),(2,1),(3,1),(4,2),(5,2),(6,2),(7,1),(8,1),(9,1);

【Answer】

It’s difficult to handle order-based computations in SQL. But it becomes much easier if we retrieve data out of the database and handle it in SPL:

A1: Retrieve the data and group it by y and x in SQL;

A2: Group A1’s records according to the rule that same continuous y values correspond continuous x values;

A3: Extract y values and corresponding continuous x ranges to generate a new table sequence.

Here’s the final result: