Oracle SQL 中的 CASE. . WHEN 表达式

我有一列的表格和以下数据

Status
a1
i
t
a2
a3

我想在我的选择查询中显示以下结果

Status| STATUSTEXT
a1    | Active
i     | Inactive
t     | Terminated
a2    | Active
a3    | Active

我可以考虑的一种方法是在选择查询中使用 Switch When 表达式

SELECT
status,
CASE status
WHEN 'a1' THEN 'Active'
WHEN 'a2' THEN 'Active'
WHEN 'a3' THEN 'Active'
WHEN 'i' THEN 'Inactive'
WHEN 't' THEN 'Terminated'
END AS StatusText
FROM stage.tst

有没有其他的方法可以让我不需要写 < B > 当表达3次为活跃状态 ，并且可以在一个表达式中检查整个活动状态？

731170 次浏览
小开

You can rewrite it to use the ELSE condition of a CASE:

SELECT status,
CASE status
WHEN 'i' THEN 'Inactive'
WHEN 't' THEN 'Terminated'
ELSE 'Active'
END AS StatusText
FROM   stage.tst
小开

Of course...

select case substr(status,1,1) -- you're only interested in the first character.
when 'a' then 'Active'
when 'i' then 'Inactive'
when 't' then 'Terminated'
end as statustext
from stage.tst

However, there's a few worrying things about this schema. Firstly if you have a column that means something, appending a number onto the end it not necessarily the best way to go. Also, depending on the number of status' you have you might want to consider turning this column into a foreign key to a separate table.

Based on your comment you definitely want to turn this into a foreign key. For instance

create table statuses ( -- Not a good table name :-)
status varchar2(10)
, description varchar2(10)
, constraint pk_statuses primary key (status)
)


create table tst (
id number
, status varchar2(10)
, constraint pk_tst primary key (id)
, constraint fk_tst foreign key (status) references statuses (status)
)

Your query then becomes

select a.status, b.description
from tst a
left outer join statuses b
on a.status = b.status

Here's a SQL Fiddle to demonstrate.

小开
最佳答案

You could use an IN clause

Something like

SELECT
status,
CASE
WHEN STATUS IN('a1','a2','a3')
THEN 'Active'
WHEN STATUS = 'i'
THEN 'Inactive'
WHEN STATUS = 't'
THEN 'Terminated'
END AS STATUSTEXT
FROM
STATUS

Have a look at this demo

SQL Fiddle DEMO

小开

You can only check the first character of the status. For this you use substring function.

substr(status, 1,1)

In your case past.

小开

Following syntax would work :

....
where x.p_NBR =to_number(substr(y.k_str,11,5))
and x.q_nbr =
(case
when instr(substr(y.m_str,11,9),'_') = 6   then  to_number(substr(y.m_str,11,5))
when instr(substr(y.m_str,11,9),'_') = 0   then  to_number(substr(y.m_str,11,9))
else
1
end
)
小开

It will be easier to do using decode.

SELECT
status,
decode ( status, 'a1','Active',
'a2','Active',
'a3','Active',
'i','Inactive',
't','Terminated',
'Default')STATUSTEXT
FROM STATUS
小开 
SELECT
STATUS,
CASE
WHEN STATUS IN('a1','a2','a3')
THEN 'Active'
WHEN STATUS = 'i'
THEN 'Inactive'
WHEN STATUS = 't'
THEN 'Terminated'  ELSE null
END AS STATUSTEXT
FROM
stage.tst;
小开

Since web search for Oracle case tops to that link, I add here for case statement, though not answer to the question asked about case expression:

CASE
WHEN grade = 'A' THEN dbms_output.put_line('Excellent');
WHEN grade = 'B' THEN dbms_output.put_line('Very Good');
WHEN grade = 'C' THEN dbms_output.put_line('Good');
WHEN grade = 'D' THEN dbms_output.put_line('Fair');
WHEN grade = 'F' THEN dbms_output.put_line('Poor');
ELSE dbms_output.put_line('No such grade');
END CASE;

or other variant:

CASE grade
WHEN 'A' THEN dbms_output.put_line('Excellent');
WHEN 'B' THEN dbms_output.put_line('Very Good');
WHEN 'C' THEN dbms_output.put_line('Good');
WHEN 'D' THEN dbms_output.put_line('Fair');
WHEN 'F' THEN dbms_output.put_line('Poor');
ELSE dbms_output.put_line('No such grade');
END CASE;

Per Oracle docs: https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/04_struc.htm

小开 
DECODE(SUBSTR(STATUS,1,1),'a','Active','i','Inactive','t','Terminated','N/A')

提交答案