select id,0 lb0,1 lb1,2 lb2,3 lb3,
sum(decode(lb,0,value,0)) value0,
sum(decode(lb,1,value,0)) value1,
sum(decode(lb,2,value,0)) value2,
sum(decode(lb,3,value,0)) value3
from z6
group by id
sum(decode(lb,0,value,0)) value0,
sum(decode(lb,1,value,0)) value1,
sum(decode(lb,2,value,0)) value2,
sum(decode(lb,3,value,0)) value3
from z6
group by id
0 lb0,
1 lb1,
2 lb2,
3 lib3,
(select nvl(sum(value),0) from a where id = 001 and lb = 0) value0,
(select nvl(sum(value),0) from a where id = 001 and lb = 1) value1,
(select nvl(sum(value),0) from a where id = 001 and lb = 2) value2,
(select nvl(sum(value),0) from a where id = 001 and lb = 3) value3
from dual
union
select 002 id,
0 lb0,
1 lb1,
2 lb2,
3 lib3,
(select nvl(sum(value),0) from a where id = 002 and lb = 0) value0,
(select nvl(sum(value),0) from a where id = 002 and lb = 1) value1,
(select nvl(sum(value),0) from a where id = 002 and lb = 2) value2,
(select nvl(sum(value),0) from a where id = 002 and lb = 3) value3
from dual 比较土,见笑了
用sqlplus>help decode 之后显示信息为“help 不可用”,大家都是通过什么来查找oracle中的函数帮助的呢?postscript:sql server 在帮助上做的很好,非常方便。
需要注意的是,这里的if、then及else 都可以是函数或计算表达式。
//**************************************************************************************
DECODE 的简单例子
Oracle系统中就有许多数据字典是使用decode 思想设计的,比如记录会话信息的V$SESSION数据字典视图就是这样。我们从《Oracle8i Reference》资料中了解到,当用户登录成功后在V$SESSION中就有该用户的相应记录,但用户所进行的命令操作在该视图中只记录命令的代码(0—没有任何操作,2—Insert…),而不是具体的命令关键字。因此,我们需要了解当前各个用户的名字及他们所进行的操作时,要用下面命令才能得到详细的结果:select sid,serial#,username,
DECODE(command,
0,’None’,
2,’Insert’,
3,’Select’,
6,’Update’,
7,’Delete’,
8,’Drop’,
‘Other’) cmmand
from v$session where username is not null;
case expression
when value1 then result1
......
else other result
思想一样,个人观点,大家讨论一下
max(decode(lb,0,s,0)) as value0,max(decode(lb,1,s,0)) as value1,
max(decode(lb,2,s,0)) as value2,max(decode(lb,3,s,0)) as value3 from(
select id,lb,sum(value) over (partition by id,lb) as s from a) group by id