表结构如下:
编号 状态
1 1
2 1
3 1
4 2
5 2
6 1
7 1
8 1
要求出统计报表结果如下:
起始编号 终止编号 记录数 记录状态
1 3 3 1
4 5 2 2
6 8 3 1
不知道该怎么写SQL语句?
编号 状态
1 1
2 1
3 1
4 2
5 2
6 1
7 1
8 1
要求出统计报表结果如下:
起始编号 终止编号 记录数 记录状态
1 3 3 1
4 5 2 2
6 8 3 1
不知道该怎么写SQL语句?
sql:
select min(code) 起始编号,
max(code) 终止编号,
count(*) 记录数,
state 记录状态
from (select code,
state,
code - row_number() over(partition by state order by code) x
from aa)
group by state, x;
SQL> select min(no) "起始编号",
2 max(no) "终止编号",
3 count(1) "记录数",
4 status "记录状态"
5 from (select no, status, row_number() over(order by status, no) rn
6 from tt
7 order by no)
8 group by status, (rn - no);
起始编号 终止编号 记录数 记录状态
---------- ---------- ---------- ----------
1 3 3 1
4 5 2 2
6 8 3 1
2 max(id) "终止编号",
3 count(1) "记录数",
4 status "记录状态"
5 from (select id, status, row_number() over(partition by status order by id) rn
6 from rpt
7 order by id)
8 group by status, (rn - id); 起始编号 终止编号 记录数 记录状态
---------- ---------- ---------- ----------
1 3 3 1
4 5 2 2
6 8 3 1