ITPub博客

首页 > Linux操作系统 > Linux操作系统 > GROUP BY CEIL 的用法

GROUP BY CEIL 的用法

原创 Linux操作系统 作者:jack198409 时间:2007-11-17 00:00:00 0 删除 编辑

SQL> WITH A AS (SELECT 'A' CD FROM DUAL
2 UNION
3 SELECT 'B' CD FROM DUAL
4 UNION
5 SELECT 'C' CD FROM DUAL
6 UNION
7 SELECT 'D' CD FROM DUAL
8 UNION
9 SELECT 'E' CD FROM DUAL
10 UNION
11 SELECT 'F' CD FROM DUAL
12 UNION
13 SELECT 'G' CD FROM DUAL
14 UNION
15 SELECT 'H' CD FROM DUAL
16 UNION
17 SELECT 'I' CD FROM DUAL
18 )
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)) ID5
24 from a
25 group by ceil(rownum / 5)
26 ;

ID1 ID2 ID3 ID4 ID5
--- --- --- --- ---
A B C D E
F G H I

例二:

with a as (select '01' ym from dual
union
select '02' ym from dual
union
select '03' ym from dual
union
select '04' ym from dual
union
select '05' ym from dual
union
select '06' ym from dual
union
select '07' ym from dual
union
select '08' ym from dual
union
select '09' ym from dual
union
select '10' ym from dual
union
select '11' ym from dual
union
select '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)) ID6
from a
group by ceil(rownum / 6)

ID1 ID2 ID3 ID4 ID5 ID6
--- --- --- --- --- ---
01 02 03 04 05 06
07 08 09 10 11 12


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10768286/viewspace-83475/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 單個文件的備份
请登录后发表评论 登录
全部评论

注册时间:2007-12-13

  • 博文量
    135
  • 访问量
    283221