create table det_200406 as select tel,mtacc,acc,
sum(decode(pitem,'P00',fee,null)) as 月租费,
sum(decode(pitem,'P01',fee,null)) as 来电显示费,
sum(decode(pitem,'P02',fee,null)) as 短信通信费,
sum(decode(pitem,'P05',fee,null)) as 秘书台198,
sum(decode(pitem,'P06',fee,null)) as 本地话费,
sum(decode(pitem,'P07',fee,null)) as 国内长途费,
sum(decode(pitem,'P08',fee,null)) as 国际长途费,
sum(decode(pitem,'P09',fee,null)) as 漫游基本费,
sum(decode(pitem,'P10',fee,null)) as 漫游国内长途费,
sum(decode(pitem,'P11',fee,null)) as 漫游国际长途费,
sum(decode(pitem,'P12',fee,null)) as 国内IP话费,
sum(decode(pitem,'P13',fee,null)) as 国际IP话费,
sum(decode(pitem,'P14',fee,null)) as 其它费,
sum(decode(pitem,'P15',fee,null)) as 滞纳金,
sum(decode(pitem,'P16',fee,null)) as 虚拟网包月费,
sum(decode(pitem,'P17',fee,null)) as 短信信息费,
sum(decode(pitem,'P18',fee,null)) as 声讯台信息费,
sum(decode(pitem,'P19',fee,null)) as 包月调整费,
sum(decode(pitem,'P20',fee,null)) as IP基本费 from det_6 group by tel,mtacc,acc;
--建表
create table stud
(
sid varchar2(10),
kcbm varchar2(10),
cj int
);
--插入测试数据
insert into stud values('1','语文',80);
insert into stud values('2','数学',90);
insert into stud values('3','英语',100);
commit;
--创建视图,decode用法
create or replace view cjd as
select sid,
decode(kcbm,'语文',cj,0) 语文,
decode(kcbm,'数学',cj,0) 数学,
decode(kcbm,'英语',cj,0) 英语
from stud
order by sid;
--显示数据
select * from cjd;
The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column rating is null, DECODE returns the value 1000:
SELECT DECODE(rating, NULL, 1000, 'C', 2000, 'B', 4000, 'A', 5000)
INTO credit_limit FROM accts WHERE acctno = my_acctno;
ENAME DEPARTMENT
---------- -------------
SMITH Research
ALLEN Sales
WARD Sales
JONES Research
MARTIN Sales
BLAKE Sales
CLARK Accounting
SCOTT Research
KING Accounting
TURNER Sales
ADAMS Research
JAMES Sales
FORD Research
MILLER Accounting