我将一系列数字存储在甲骨文表中,格式如下。
Emp_ID
1
2
3
4
5
6
7
8
9
10
14
15
16
17
18
31
32
33
34
35
36
41
42
我希望将此列表按固定数字分组,如7,并以以下格式获得输出:
Range Total
1-7 7
8-10,14-17 7
18-18,31-36 7
41-42 2
发布于 2016-01-13 12:07:18
如果需要范围表示,如示例中所示:
-- test data
with data(empid) as
( -- complete list 1..42
select level
from dual
connect by level <= 42
minus (
-- minus gaps
select level
from dual
where level between 11 and 13
connect by level <= 42
union
select level
from dual
where level between 19 and 30
connect by level <= 42
union
select level
from dual
where level between 37 and 40
connect by level <= 42))
-- select:
select listagg(case
when minempid = maxempid then
minempid || ' '
else
(minempid || '-' || maxempid)
end,
', ') within group(order by minempid),
sum(cnt)
from (select grp,
seq,
min(empid) as minempid,
max(empid) as maxempid,
count(*) cnt
from (select empid, rn, empid - rn as seq, ceil(rn / 7) as grp
from (select empid, row_number() over(order by empid) rn
from data))
group by grp, seq)
group by grp;
发布于 2016-01-13 16:02:47
另一个例子是使用大小写确定每个值的范围,并对该范围进行分组:
select rng, count(1) from (
select
case
when emp_id between 1 and 7 then 'range 1'
when emp_id between 8 and 10 or emp_id between 14 and 17 then 'range 2'
when emp_id between 18 and 18 or emp_id between 31 and 36 then 'range 3'
when emp_id between 41 and 42 then 'range 4'
else 'no range'
end as rng
from employees)
group by rng;
https://stackoverflow.com/questions/34765167
复制相似问题