ITPub博客

首页 > IT职业 > IT生活 > DECODE 的函数的学习

DECODE 的函数的学习

原创 IT生活 作者:xxgoracle 时间:2005-01-18 23:24:59 0 删除 编辑
开始学习DECODE函数来对数据进行操作,掌握她的用法,方便进行分组、筛选数据[@more@]

DECODE函数使用格式:

DECODE('values','if_condition_1','then_result_1', 'if_condition_2','then_result_2','if_condition_3', 'then_result_3',........,'else_result')

ForExample:

☆.用于显示每个图书分类的每个人的最少、最多和平均借阅 时间的报表>

> select B.CategoryName, MAX(decode(BC.name,'FRED FULLER', BC.ReturnedDate-BC.CheckOutDate,NULL)) MaxFF,

AVG(decode(BC.Name,'FRED FULLER', BC.ReturnedDate-BC.CheckOutDate,NULL)) AvgFF,

MAX(decode(BC.Name,'DORAH TALBOT', BC.ReturnedDate-BC.CheckOutDate,NULL)) MaxDT,

AVG(decode(BC.Name,'DORAH TALBOT', BC.ReturnedDate-BC.CheckOutDate,NULL)) AvgDT,

MAX(decode(BC.Name,'GERHARDT KENTGEN', BC.ReturnedDate-BC.CheckOutDate,NULL)) MaxGK,

AVG(decode(BC.Name,'GERHARDT KENTGEN', BC.returneddate-BC.CheckOutDate,NULL)) AvgGK

from BOOKSHELF_CHECKOUT BC,BOOKSHELF B where BC.Title = B.Title group by B.CategoryName;

☆.通过decode替换值

>select distinct decode(CategoryName,'ADULTFIC','Adult Fiction', 'ADULTNF','Adult Nonfiction', 'ADULTREF',

'Adult Reference', 'CHILDRENFIC','Children Fiction', 'CHILDRENPIC',

'Children Picturebook', CategoryName)

from BOOKSHELF;

☆.decode中的decode

>select BC.Name,BC.Title,BC.ReturnedDate, BC.ReturnedDate-BC.CheckOutDate as DaysOut /*Count days*/,

decode(SUBSTR(CategoryName,1,5),'ADULT',

BC.ReturnedDate-BC.CheckOutDate-21,

BC.ReturnedDate-BC.CheckOutDate-14) DaysLate,

decode(SUBSTR(CategoryName,1,5),'ADULT',

decode(SUBSTR(CategoryName,6,3),'FIC',

(BC.ReturnedDate-BC.CheckOutDate-21)*0.60,

(BC.ReturnedDate-BC.CheckOutDate-21)*0.30),

(BC.ReturnedDate-BC.CheckOutDate-14)*0.20) LateFee

from BOOLSHELF_CHECKOUT BC,BOOKSHELF B

where BC.Title = B.Title and BC.ReturnedDate-BC.CheckOutDate > decode(SUBSTR(CategoryName,1,5),'ADULT',21,14)

order by BC.Name,BC.CheckOutDate;

☆.decode中的大于和小于

★一个使用SQL:

select t.id,
decode(t.id,'0004000000',t.col4,
'0004000001',t.col4,
'0004000002',t.col4,
'0004000100',t.col4,
'0004000101',t.col4,
'0004000102',t.col4,
'0004000103',t.col4,
'0004000200',t.col4,
'0004000201',t.col4,
'0004000202',t.col4,
'0004000203',t.col4,
'0004000204',t.col4,
'0004000300',t.col4,
'0004000301',t.col4,
'0004000302',t.col4,
'0004000303',t.col4,
'00040100',t.col3,
'00040101',t.col3,
'00040102',t.col3,
'00040103',t.col3,
'0004020000',t.col3,
'0004020001',t.col3,
'0004020002',t.col3,
'0004020003',t.col3,
'00040201',t.col3,
'00040202',t.col3,
'00040203',t.col3,
'00040204',t.col3,
'0004020500',t.col3,
'0004020501',t.col3,
'0004020502',t.col3,
'0004020503',t.col3,
'00040300',t.col4,
'00040301',t.col4,
'00040302',t.col4,
'00040303',t.col4,
NULL) AS GZD
from tab1 t
where t.ActiveDate >= to_date('2006-08-01','YYYY-MM-DD')
and t.ActiveDate < to_date('2006-08-30','YYYY-MM-DD')
and t.id like '0004%'

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

下一篇: 分组函数的学习
请登录后发表评论 登录
全部评论
  • 博文量
    37
  • 访问量
    519396