首页 > 数据库 > mysql教程 > Oracle子句GROUP BY CUBE续

Oracle子句GROUP BY CUBE续

WBOY
发布: 2016-06-07 17:17:55
原创
1125 人浏览过

上篇日志《ORACLE子句GROUP BY CUBE》中的问题稍作变化,现在每个字段有3个状态,SQL该如何写呢?这时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 level as le
         from dual
       connect by level
       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 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

linux

来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板