根据值分割一行 [英] Split a row based on a value

问题描述

我的Oracle数据库中有下表

I have the following table in my Oracle database

TopCode BottomCode  Totalpeep
A   ABC123      100
B   BED456           45

我想要一个能够基于Totalpeep/20分割行的查询即使Totalpeep小于20，A也会有5组，B会有3组

I want a query that is to able to split the rows based on the Totalpeep / 20 e.g. there will be 5 groups for A and 3 groups for B, even if the Totalpeep is less than 20 then this must have one group

TopCode  BottomCode  Grp
A        ABC123      A1
A        ABC123      A2
A        ABC123      A3
A        ABC123      A4
A        ABC123      A5
B        BED456      B1
B        BED456      B2
B        BED456      B3

推荐答案

易于使用connect by实现:

Easy to achieve using connect by:

with sample_data (TopCode, BottomCode, Totalpeep) as (select 'A', 'ABC123', 100 from dual union all
                                                      select 'B', 'BED456', 45 from dual)
select topcode,
       bottomcode,
       topcode||level grp
from   sample_data
connect by prior topcode = topcode
           and prior dbms_random.value is not null
           and level <= ceil(totalpeep/20);

TOPCODE BOTTOMCODE GRP                                      
------- ---------- ----
A       ABC123     A1
A       ABC123     A2
A       ABC123     A3
A       ABC123     A4
A       ABC123     A5
B       BED456     B1
B       BED456     B2
B       BED456     B3

这篇关于根据值分割一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！