使用Oracle的Decode函数进行多值判断

decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。以下是一个简单测试，用于说明Decode函数的用法:

select pv.name, pv.cost_type, decode(pv.cost_type, 2, '包时段', '按次扣费') decode from product_version pv order by pv.product_id

[img]http://dl.iteye.com/upload/attachment/406217/98004341-18f2-3079-a32d-448650b14f27.png[/img]

select pv.name, pv.cost_type, decode(pv.cost_type, 2, '包时段') as decode from product_version pv order by pv.product_id

[img]http://dl.iteye.com/upload/attachment/406221/65c194c2-ffed-3a5f-abac-eace0553e93b.png[/img]


[b]使用DECODE函数来减少处理时间[/b]
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. 例如:

select count(pv.product_id) from product_version pv where pv.cost_type=2

[img]http://dl.iteye.com/upload/attachment/406231/74127a65-0135-333c-870d-5aac8fdabf75.png[/img]

select count(pv.product_id) from product_version pv where pv.cost_type=1

[img]http://dl.iteye.com/upload/attachment/406233/583b0ed1-c2e9-3bc4-9f57-ccb4397f9282.png[/img]

你可以用DECODE函数高效地得到相同结果:

select count(decode(pv.cost_type, 2, '包时段', NULL)),count(decode(pv.cost_type, 1, '按次扣费', NULL)) from product_version pv

[img]http://dl.iteye.com/upload/attachment/406235/40a3b971-392b-3525-9be5-03ea190ed58d.png[/img]