Oracle Decode小试

create table USERINFO
(
  AGE  INTEGER,
  NAME VARCHAR2(50)
)

insert into USERINFO (AGE, NAME) values (12, 'jack');
insert into USERINFO (AGE, NAME) values (13, 'jack');
insert into USERINFO (AGE, NAME) values (20, 'john');
insert into USERINFO (AGE, NAME) values (23, 'andy');
insert into USERINFO (AGE, NAME) values (25, 'mark');
insert into USERINFO (AGE, NAME) values (14, 'mary');


1,查询名称重复的记录
select name,count(*) from userinfo group by name having count(*) > 1

2,将小于18岁的显示成未成年
select name,age,decode(sign(age-18),1,to_char(age),0,to_char(age),-1,'未成年') age1 from userinfo

select name,age,case sign(age-18)
       when 1 then to_char(age)
       when 0 then to_char(age)
       else '未成年'end from userinfo

decode 函数 是延用Oracle8的，以后版本 支持case when。
Oracle8 不支持left join, right join,inner join