Oracle如何使用在WHERE条件的列中指定的字段

Question

我将此表放入Oracle数据库中

+----+---+---+---+---+-------------+
| ID | A | B | C | D | CheckColumn |
+----+---+---+---+---+-------------+
|  1 | X |   |   |   | A           |
|  2 |   | X |   |   | A           |
|  3 |   |   | X |   | A           |
|  4 |   |   | X |   | C           |
|  5 |   | X |   |   | B           |
|  6 |   |   |   | X | B           |
+----+---+---+---+---+-------------+

其中CheckColumn列指定了我必须用于生成where的文件。换句话说，我想做一个像这样的查询

SELECT *
FROM Table
WHERE *Column of CheckColumn for that row*='X'

并且结果集应该是以下

+----+---+---+---+---+-------------+
| ID | A | B | C | D | CheckColumn |
+----+---+---+---+---+-------------+
|  1 | X |   |   |   | A           |
|  4 |   |   | X |   | C           |
|  5 |   | X |   |   | B           |
+----+---+---+---+---+-------------+

如何在SELECT中使用CheckColumn的值？

Answer 1

您可以使用DECODE将CheckColumn映射到右侧列

select * from tab
where 
decode(checkColumn,'A',A,'B',B,'C',C,'D',D) = 'X';

Answer 2

在我看来，它是关于动态SQL的。阅读：PL/SQL。

例如：

SQL> select * from test;
ID A B C D C
---------- - - - - -
1 X       A
2   X     A
3     X   A
4     X   C
5   X     B
6       X B
6 rows selected.

功能：

SQL> create or replace function f_test
2    return sys_refcursor
3  is
4    l_str varchar2(4000);
5    rc    sys_refcursor;
6  begin
7    for cur_r in (select distinct checkcolumn from test) loop
8      l_str := l_str || 'select * from test where checkcolumn = ' || chr(39) ||
9               cur_r.checkcolumn || chr(39) ||' and ' || cur_r.checkcolumn || ' = ''X'' union all ';
10    end loop;
11
12    l_str := rtrim(l_str, ' union all ');
13    open rc for l_str;
14    return rc;
15  end;
16  /
Function created.

测试：

SQL> select f_test from dual;
F_TEST
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
ID A B C D C
---------- - - - - -
1 X       A
4     X   C
5   X     B

SQL>

Answer 3

一种方法是大量的布尔逻辑：

where (checkcolumn = 'A' and a = 'X') or
(checkcolumn = 'B' and b = 'X') or
(checkcolumn = 'C' and c = 'X') or
(checkcolumn = 'D' and d = 'X')