DECODE 的函数的学习

最新推荐文章于 2020-04-06 18:00:28 发布

congzhiba7952

最新推荐文章于 2020-04-06 18:00:28 发布

阅读量167

点赞数

开始学习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%'