首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >如何在oracle中对一系列数字进行分组,并提供特定的数字

如何在oracle中对一系列数字进行分组,并提供特定的数字
EN

Stack Overflow用户
提问于 2016-01-13 11:12:56
回答 2查看 358关注 0票数 0

我将一系列数字存储在甲骨文表中,格式如下。

代码语言:javascript
运行
复制
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,并以以下格式获得输出:

代码语言:javascript
运行
复制
Range         Total
1-7            7
8-10,14-17     7
18-18,31-36    7
41-42          2
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2016-01-13 12:07:18

如果需要范围表示,如示例中所示:

代码语言:javascript
运行
复制
-- 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;
票数 0
EN

Stack Overflow用户

发布于 2016-01-13 16:02:47

另一个例子是使用大小写确定每个值的范围,并对该范围进行分组:

代码语言:javascript
运行
复制
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;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/34765167

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档