分组计算连续区间

【问题】

Can I group values of an another 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);

【回答】

SQL做这种序相关的运算很难想清，读出来在外部写容易得多，用SPL就很简单：

	A
1	=$select x,y from tb order by y,x
2	=A1.group@i(y!=y[-1] || x!=x[-1]+1)
3	=A2.new(y,concat(~.m(1).x,"-",~.m(-1).x):range)

A1：sql取数，并按照y，x排序

A2：按照同一组中y相同，x连续的规则分组

A3：从每组中提取y和x的范围组成新的序表，结果为：