oracle中 decode()函数

decode()函数简介：

主要作用：将查询结果翻译成其他值（即以其他形式表现出来，以下举例说明）；

使用方法：

Select decode（columnname，值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值）

From talbename

Where …

其中columnname为要选择的table中所定义的column，

·含义解释：

decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)的理解如下：

if （条件==值1）

then　　　　

return(翻译值1)

elsif （条件==值2）

then　　　　

return(翻译值2)　　　　

……

elsif （条件==值n）

then　　　　

return(翻译值n)

else　　　　

return(缺省值)

end if

注：其中缺省值可以是你要选择的column name 本身，也可以是你想定义的其他值，比如Other等；

例子：

-- 创建一个计划表
drop table  if EXISTS plan;
create table plan (
    id int PRIMARY key,
    name varchar(255)
);


-- 创建一个计划明细表，是计划表的子表
drop table  if EXISTS planDetail;
create table planDetail(
    id int PRIMARY key,
    name varchar(255),
    state int,
    planId int

);


-- 插入数据
insert into plan values(1,'计划一');
insert into plan values(2,'计划二');
insert into plan values(3,'计划三');

insert into planDetail values(1,'明细一',0,1);
insert into planDetail values(2,'明细2',1,1);
insert into planDetail values(3,'明细3',1,1);

insert into planDetail values(4,'明细一',0,2);
insert into planDetail values(5,'明细2',0,2);
insert into planDetail values(6,'明细3',1,2);

insert into planDetail values(7,'明细一',1,3);
insert into planDetail values(8,'明细2',1,3);
insert into planDetail values(9,'明细3',1,3);

现在要写出一条sql语句，获取每条计划的id，name和该计划的明细state为0的明细数量、state为1的明细数量以及state为0或者1的数量。

如果不使用decode（）函数，用left join来写的话：

select  t.*,a.sum1,b.sum2,c.sum3  from (

select t1.id,t1.name from plan t1
LEFT JOIN planDetail t2 on t2.planid = t1.id 
GROUP BY t1.id,t1.name) t

LEFT JOIN (
-- state = 0的数量
select  t1.id,t1.name, count(t2.id) sum1 from plan t1
LEFT JOIN planDetail t2 on t2.planid = t1.id
where t2.state = 0
GROUP BY t1.id,t1.name ) a on t.id = a.id

LEFT JOIN (
-- state = 1的数量
select  t1.id,t1.name, count(t2.id) sum2 from plan t1
LEFT JOIN planDetail t2 on t2.planid = t1.id
where t2.state = 1
GROUP BY t1.id,t1.name ) b on t.id = b.id

LEFT JOIN (
-- state = 1或 state = 0的数量
select  t1.id,t1.name, count(t2.id) sum3 from plan t1
LEFT JOIN planDetail t2 on t2.planid = t1.id
where t2.state = 1 or t2.state = 0
GROUP BY t1.id,t1.name) c on t.id = c.id

需要分别写一个sql语句来计算state不同状态的明细数量然后全部连接起来。sql语句比较长。

而使用decode（）时就比较简单

select  t1.id,t1.name, sum(decode(state,0,1,0)) sum1,sum(decode(state,1,1,0)) sum2,sum(decode(state,0,1,1,1,0)) sum3 from plan t1
LEFT JOIN planDetail t2 on t2.planid = t1.id
where t2.state = 1 or t2.state = 0
GROUP BY t1.id,t1.name

了