我要统计一个数据,比如有个表table 里面有2个字段
start end count sum
11113 11117 5 10
11121 11123 3 9语句能够实现:start-end sum/count
11113 2
11114 2
11115 2
11116 2
11117 2
11121 3
11122 3
11123 3帮帮忙啊!谢谢!
start end count sum
11113 11117 5 10
11121 11123 3 9语句能够实现:start-end sum/count
11113 2
11114 2
11115 2
11116 2
11117 2
11121 3
11122 3
11123 3帮帮忙啊!谢谢!
2 union all select 11121,11123,3,9 from dual
3 )select distinct "START"+level-1 as "START",sum/count from tt connect by level<=end-"START"+1 order by 1;
START SUM/COUNT
---------- ----------
11113 2
11114 2
11115 2
11116 2
11117 2
11121 3
11122 3
11123 3
8 rows selected
SQL> with tt as(select 11113 as "START",11117 end,5 count,10 sum from dual
2 union all select 11121,11123,3,9 from dual
3 )select distinct "START"+level-1 as "start-end",sum/count as "sum/count" from tt connect by level<=end-"START"+1 order by 1;
start-end sum/count
---------- ----------
11113 2
11114 2
11115 2
11116 2
11117 2
11121 3
11122 3
11123 3
8 rows selected