用一个实例讲解GROUP BY CEIL的使用方法

SQL> WITH A AS (SELECT 'A' CD FROM DUAL2 UNION3 SELECT 'B' CD FROM DUAL4 UNION5 SELECT 'C' CD FROM DUAL6 UNION7 SELECT 'D' CD FROM DUAL8 UNION9 SELECT 'E' CD FROM DUAL10 UNION11 SELECT 'F' CD FROM DUAL12 UNION13 SELECT 'G' CD FROM DUAL14 UNION15 SELECT 'H' CD FROM DUAL16 UNION17 SELECT 'I' CD FROM DUAL18 )19 select max(decode(mod(rownum, 5), 1, CD, null)) ID1,20 max(decode(mod(rownum, 5), 2, CD, null)) ID2,21 max(decode(mod(rownum, 5), 3, CD, null)) ID3,22 max(decode(mod(rownum, 5), 4, CD, null)) ID4,23 max(decode(mod(rownum, 5), 0, CD, null)) ID524 from a25 group by ceil(rownum / 5)26 ;ID1 ID2 ID3 ID4 ID5--- --- --- --- ---A B C D EF G H I 例二：with a as (select '01' ym from dualunionselect '02' ym from dualunionselect '03' ym from dualunionselect '04' ym from dualunionselect '05' ym from dualunionselect '06' ym from dualunionselect '07' ym from dualunionselect '08' ym from dualunionselect '09' ym from dualunionselect '10' ym from dualunionselect '11' ym from dualunionselect '12' ym from dual)select max(decode(mod(rownum, 6), 1, ym, null)) ID1,max(decode(mod(rownum, 6), 2, ym, null)) ID2,max(decode(mod(rownum, 6), 3, ym, null)) ID3,max(decode(mod(rownum, 6), 4, ym, null)) ID4,max(decode(mod(rownum, 6), 5, ym, null)) ID5,max(decode(mod(rownum, 6), 0, ym, null)) ID6from agroup by ceil(rownum / 6)ID1 ID2 ID3 ID4 ID5 ID6--- --- --- --- --- ---01 02 03 04 05 0607 08 09 10 11 12