用于对数据进行分组的 SQL 查询

Question

我有一个类似下面的数据集。

Code          Values
521            1
522            1
523            1
524            0
525            0
526            1
527            1

我期待如下所示的输出:

Group     CD        Values
G1      521-523       1
G2      524-525       0
G3      526-527       1

Answer 1

我倾向于使用行号差异方法:

select concat('G', row_number() over (order by min(value)) as grp,
       concat(min(code), '-', max(code)) as codes,
       value
from (select t.*,
             row_number() over (order by code) as seqnum_c,
             row_number() over (partition by value order by code) as seqnum_vc
      from t
     ) t
group by value, (seqnum_c - seqnum_vc);