科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网网络频道用一个实例讲解GROUP BY CEIL的使用方法

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

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

本文将用一个实例讲解GROUP BY CEIL的使用方法。

作者:赛迪网 孙诗涵 来源:天新网 2008年3月22日

关键字: Mssql SQL Server SQL 数据库

  • 评论
  • 分享微博
  • 分享邮件

GROUP BY CEIL的使用方法:

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
    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章