使用 Oracle 子选择替换 CASE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1134899/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using a Oracle subselect to replace a CASE statement
提问by shermy
Hy guys,
嘿伙计们，
can anybody please help me with a subquery in Oracle database 10g? I need to extract the values for a column in the first table as value of another column in the second table. I currently use this statement:
任何人都可以帮我处理 Oracle 数据库 10g 中的子查询吗？我需要提取第一个表中一列的值作为第二个表中另一列的值。我目前使用这个语句：
SELECT
CASE WHEN A.column1 = 'A' THEN 'aaa'
WHEN A.column1 = 'B' THEN 'bbb'
.......
WHEN A.column1 = 'X' THEN 'xxx'
ELSE 'bad' END AS COLUMN1, A.*
FROM TRANSACTION_TABLE A, CATEGORY_TABLE B
WHERE A.column1 IS NOT NULL
AND A.column1 <> ' '
This is not an elegant approach, so I'm trying to use a subselect from CATEGORY_TABLE B like the following:
这不是一种优雅的方法，因此我尝试使用 CATEGORY_TABLE B 中的子选择，如下所示：
SELECT A.column1, A.*
FROM TRANSACTION_TABLE A, CATEGORY_TABLE B
WHERE A.column1 IS NOT NULL
AND A.column1 = B.column_b_1
AND A.column1 <> ' '
AND A.column1 IN (SELECT B.column_b_1_descr FROM CATEGORY_TABLE B
WHERE B.FIELDNAME = 'column1' AND A.column1 = B.column_b_1)
So, I cannot get any results by using the subquery and don't want to continue using the CASE against many conditions, just want to replace the A.column1 values with the descriptive values from B.column_b_1_descr , as they're easier to read. I would appreciate any feedback. Thanks
因此，我无法通过使用子查询获得任何结果，并且不想在许多条件下继续使用 CASE，只想用 B.column_b_1_descr 中的描述性值替换 A.column1 值，因为它们更易于阅读. 我将不胜感激任何反馈。谢谢
回答by Tim Sylvester
Unless I'm misunderstanding your question...
除非我误解了你的问题......
CATEGORY_TABLE:
name | value
A aaa
B bbb
C ccc
...
SELECT B.value AS COLUMN1, A.\*
FROM TRANSACTION\_TABLE A, CATEGORY\_TABLE B
WHERE A.column1 = B.name
or
或者
SELECT t2.value as COLUMN1, t1.\*
FROM TRANSACTION\_TABLE t1
INNER JOIN CATEGORY\_TABLE t2 ON t1.column1 = t2.name;
The where clause isn't needed, since an inner join automatically excludes rows with null values or no matches.
不需要 where 子句，因为内部联接会自动排除具有空值或不匹配的行。
