ORACLE子句GROUP BY CUBE续

上篇日志《ORACLE子句GROUP BY CUBE》中的问题稍作变化，现在每个字段有3个状态，SQL该如何写呢？这时CUBE的机制恐怕没有办法构成3的八次方行数据了，只能另寻办法。以下代码是以3列为例的一个比较啰嗦的方案，仅供参考。

with base as

(select 0 as a1, 0 as a2, 0 as a3

    from dual

  union all

  select 1 as a1, 0 as a2, 0 as a3

    from dual

  union all

  select 9 as a1, 0 as a2, 0 as a3 from dual)

select b.a1,decode(c.le,1,0,2,1,3,9) as a2,decode(d.le,1,0,2,1,3,9) as a3

  from base b,

       (select level as le

          from dual

        connect by level < (select count(1) + 1 from base)) c,

        (select level as le

          from dual

        connect by level < (select count(1) + 1 from base)) d

        order by a1,a2,a3

 

惭愧，有点低级错误啦。修改后代码如下：

with base as

 (select 0 as a1, 0 as a2, 0 as a3

    from dual

  union all

  select 1 as a1, 0 as a2, 0 as a3

    from dual

  union all

  select 9 as a1, 0 as a2, 0 as a3 from dual),

mult as

 (select level as le

    from dual

  connect by level < (select count(1) + 1 from base))

select b.a1,

       decode(m1.le, 1, 0, 2, 1, 3, 9) as a2,

       decode(m2.le, 1, 0, 2, 1, 3, 9) as a3

  from base b, mult m1, mult m2

 order by a1, a2, a3

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

转载于:http://blog.itpub.net/24867611/viewspace-736083/