对指定表的若干行记录按照某字段进行分段

Question

大家好，这几天项目上遇到了一个问题。描述如下：
在Student表中，有ID, State等很多字段。ID是主键，State是学生的状态，一共有5中可能的取值。
现在我想最指定ID区域的记录，按照State进行统计。
比如：在ID为1000到1100的记录中，我需要一个函数来完成的功能是：将这100条记录进行分段输出。
输出的结果可以如下：
ID        State
----------------
1001      0
1003      1
1008      2
1010      1
1020      0
...       ...
或者
ID_Begin  ID_End    State
-------------------------
1001      1002      0
1003      1007      1
1008      1009      2
1010      1019      1
1020      1030      2
...       ...
也就是当State相等时，只输出ID最小的记录。或者输出State相等的从ID_Begin到ID_End的记录。
注意：是分段输出，不是按类型输出。单用distant是不行的。其实这个问题可以转换成字符串输出的问题，我现在的程序是使用循环逐行比较然后输出，希望能找到更好的算法！

Answer 1

这是例子，你改改就行--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
'a',2
'a',3
'a',6
'a',7
'a',8
'b',3
'b',5
'b',6
'b',7
GO--已用编号分布查询
SELECT col1,start_col2=col2,
    end_col2=(
        SELECT MIN(col2) FROM tb aa
        WHERE col1=a.col1 AND col2>=a.col2 
            AND NOT EXISTS(
                SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2+1))
FROM tb a
WHERE NOT EXISTS(
    SELECT * FROM tb WHERE col1=a.col1 and col2=a.col2-1)
/*--结果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          2           3
a          6           8
b          3           3
b          5           7
--*/*/

Answer 2

try this:select a.id ID_Begin, (b.id - 1) ID_End, a.state state
  from (select id, state, row_number() over(order by id) idd from student) a,
       (select id, state, row_number() over(order by id) - 1 idd
          from student) b
 where a.idd = b.idd
 order by a.id

Answer 3

做了一个实验，应该是lz想要的结果。drop table student;create table student(
id varchar2(4),
status int
);begin
for i in 1000..1100
loop
insert into student values(i,mod(floor(i/6),5));
end loop;
commit;
end;
with x as
(select a.id id_begin,b.id id_end,a.status
from
student a,student b
where a.status=b.status and a.id<=b.id
and not exists(select 1 from student d where a.id<=d.id and d.id<=b.id and a.status!=d.status)
)
select * from x x1 
where 
not exists
(select 1 from x x2 
where (x2.id_begin<x1.id_begin and x2.id_end>=x1.id_begin or x2.id_begin<=x1.id_begin and x2.id_end >x1.id_end )
and x1.status=x2.status)  
order by 1
查询结果
ID_B ID_E     STATUS
---- ---- ----------
1000 1001          1
1002 1007          2
1008 1013          3
1014 1019          4
1020 1025          0
1026 1031          1
1032 1037          2
1038 1043          3
1044 1049          4
1050 1055          0
1056 1061          1
1062 1067          2
1068 1073          3
1074 1079          4
1080 1085          0
1086 1091          1
1092 1097          2
1098 1100          318 rows selected.