select * from(
with T_CUST as
(
select 1 CUST_NO, '1' CUST_TYPE from dual
union all
select 2,'A' from dual
union all
select 3,'M' from dual
union all
select 4,'C' from dual
union all
select 5,'A' from dual
union all
select 6,'M' from dual
union all
select 7,'3' from dual
)
select case CUST_TYPE
when 'A' or '1' then 'HP'
when 'C' or '3' then 'DELL'
else 'IBM'
end as name
from T_CUST
)
多谢指点迷津
with T_CUST as
(
select 1 CUST_NO, '1' CUST_TYPE from dual
union all
select 2,'A' from dual
union all
select 3,'M' from dual
union all
select 4,'C' from dual
union all
select 5,'A' from dual
union all
select 6,'M' from dual
union all
select 7,'3' from dual
)
select case CUST_TYPE
when 'A' or '1' then 'HP'
when 'C' or '3' then 'DELL'
else 'IBM'
end as name
from T_CUST
)
多谢指点迷津
with T_CUST as
(
select 1 CUST_NO, '1' CUST_TYPE from dual
union all
select 2,'A' from dual
union all
select 3,'M' from dual
union all
select 4,'C' from dual
union all
select 5,'A' from dual
union all
select 6,'M' from dual
union all
select 7,'3' from dual
)
SELECT DECODE(CUST_TYPE, 'A', 'HP', '1', 'HP', 'C', 'DELL', '3', 'DELL', 'IBM')
AS NAME
from T_CUST
)
when 'A' or '1' then 'HP'
when 'C' or '3' then 'DELL'
else 'IBM'
end as name
from T_CUST
)
这段有语法错误,要这样写case when CUST_TYPE='A' or CUST_TYPE='1' then 'HP' 我个人的理解,你那种写法,when 后边应该是CUST_TYPE的值,是不能包含表达式的。
select *
from (with T_CUST as (select 1 CUST_NO, '1' CUST_TYPE
from dual
union all
select 2, 'A'
from dual
union all
select 3, 'M'
from dual
union all
select 4, 'C'
from dual
union all
select 5, 'A'
from dual
union all
select 6, 'M'
from dual
union all
select 7, '3' from dual)
select (case
when CUST_TYPE in ('A', '1') then
'HP'
when CUST_TYPE in ('C', '3') then
'DELL'
else
'IBM'
end) as name
from T_CUST);