http://www.itpub.net/thread-1873736-1-1.html

create table test (id int,name varchar(10));

insert into test values(11,'aaa');

insert into test values(12,'aaa');

insert into test values(13,'aaa');

insert into test values(15,'aaa');

insert into test values(16,'bbb');

insert into test values(17,'bbb');

insert into test values(18,'ccc');

insert into test values(19,'zzz');

insert into test values(20,'zzz');

Oracle:

SELECT name, decode(COUNT(*), 1, to_char(MIN(id)), MIN(id) || '-' || MAX(id)) AS b

FROM (

    SELECT id, name, MAX(rn) OVER (PARTITION BY name ORDER BY id) AS rn

    FROM (

        SELECT id, name, decode(id - LAG(id) OVER (PARTITION BY name ORDER BY id), 1, 0, id) AS rn

        FROM test

    )

)

GROUP BY name, rn

ORDER BY name, rn;

SELECT NAME, decode(COUNT(*), 1, to_char(MIN(id)), MIN(id) || '-' || MAX(id)) AS b

FROM (

    SELECT t.*, id - ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ID) AS gp

    FROM TEST t

)

GROUP BY NAME, gp

ORDER BY NAME;

MySQL

SELECT name, CASE WHEN COUNT(*) = 1 THEN MIN(id) ELSE concat(MIN(id), '-', MAX(id)) END AS r

FROM (SELECT id, name, id - (

            SELECT COUNT(*)

            FROM test t1

            WHERE t1.name = t2.name

                AND t1.id <= t2.id

            ) AS rn

    FROM test t2

    ORDER BY name, id

    ) t1

GROUP BY name, rn

ORDER BY name;

用相關子查詢實現Oracle row_number分析函式的功能，其餘一樣。

SQL根據分組求連續範圍

相關文章