现在有这么一个需求,需要合并序号连续且内容相同的记录范围,原始数据如下所示:
Id status
1 good
2 bad
3 good
4 good
5 good
6 bad
7 bad输出的效果如下:id范围 status 相同的个数
1 good 1
2 bad 1
3-5 good 3
6-7 bad 2要达到这种效果,一条SQL语句能否实现?
Id status
1 good
2 bad
3 good
4 good
5 good
6 bad
7 bad输出的效果如下:id范围 status 相同的个数
1 good 1
2 bad 1
3-5 good 3
6-7 bad 2要达到这种效果,一条SQL语句能否实现?
ID NUMBER(20),
val varchar2(20)
);
INSERT INTO TEST_a VALUES(1,'good');
INSERT INTO TEST_a VALUES(2,'bad');
INSERT INTO TEST_a VALUES(3,'good';
INSERT INTO TEST_a VALUES(4,'good');
INSERT INTO TEST_a VALUES(5,'good');
INSERT INTO TEST_a VALUES(6,'good');
INSERT INTO TEST_a VALUES(7,'bad');
INSERT INTO TEST_a VALUES(8,'bad');
commit;
SELECT min(id)||'-'||(min(id)+COUNT(*)-1) ids,val,COUNT(*) FROM
(
SELECT ID,val,
row_number() OVER(ORDER BY ID)-row_number() OVER(PARTITION BY val ORDER BY ID) x
FROM TEST_a
)
GROUP BY val,x
ORDER BY MIN(ID);