SQL：搜索包含给定值的列列表（在行内）

Question

我有一个包含很多列的表格。有没有办法制作一个查询来回答这个问题：“对于特定的_id（主键），这一行中哪些字段的值为10”？

编辑：

澄清：表格已经设置正确。我正在进行一些手动查询，以跟踪一些不正确的数据。该表已经被优化，以便最快地处理自动化查询，这代表了运行的绝大部分查询。（而且对于超过9500万行的表格，每一点优化都非常重要）

我意识到我的问题要求SQL做一些它本来不打算做的事情。我只是希望有一些技巧可以得到我想要的结果。

为了后人而编辑：

在我们的系统中，我们有许多不同的用户帐户。其中一个帐户是我们用于所有只读查询的账户（这是我大多数情况下使用的账户）。它不拥有相关表格，因此当我将答案适应到我的情况时，我必须进行以下更改： USER_TAB_COLUMNS 必须变成 ALL_TAB_COLUMNS，并且我必须向查询中添加 OWNER ='[OWNER]' 。

- David Oneill

Answer 1

这不是普通的数据库功能，但你不是第一个提出这种要求的人。

解决方案需要两个东西。第一个是数据字典；Oracle数据库不支持反射，但它提供了一组视图，可以提供有关数据库对象的元数据。在这种情况下，我们需要user_tab_columns，它将为我们提供给定表格的列。第二个是动态SQL；这是在运行时组装SQL查询然后执行它的能力。有几种做法，但通常ref cursor足够了。

以下代码是概念验证。它需要四个参数：

1. 您想搜索的表的名称
2. 该表的主键列的名称
3. 您想要限制的主键值
4. 您想要搜索的值。

这只是个简单的示例，您可能需要编辑它以整理输出或使程序更加灵活。

create or replace procedure search_cols
  (tname in user_tables.table_name%type
   , pk_col in user_tab_columns.column_name%type
   , pk in number
   , val in number )
is
    firstcol boolean := true;
    stmt varchar2(32767);
    result varchar2(32767);
    rc sys_refcursor;
begin
    stmt := 'select ';
    << projection >>
    for lrec in ( select column_name from user_tab_columns
                  where table_name = tname
                  and column_name != pk_col
                  and data_type = 'NUMBER'
                  order by column_id )
    loop
        if not firstcol then
            stmt := stmt || chr(10) || '||'',''||';
        else
            firstcol := false;
        end if;
        stmt := stmt || ' case when '|| lrec.column_name||' = '|| val ||
                           ' then '''|| lrec.column_name || ''' else null end';
    end loop projection;
    stmt := stmt || chr(10)|| ' from '||tname||' where '|| pk_col || ' = '|| pk;
    --  dbms_output.put_line(stmt);
    open rc for stmt;
    fetch rc into result;
    close rc;
    dbms_output.put_line(tname || '::' || val || ' found in '||result);
end search_cols;
/

正如您所见，动态SQL很难阅读。调试也更加困难 :) 所以最好有一种方法来显示最终语句。

无论如何，这里是结果：

SQL> set serveroutput on size unlimited
SQL> exec search_cols('T23', 'ID', 111, 10)
T23::10 found in ,COL_B,COL_C,

PL/SQL procedure successfully completed.

SQL> exec search_cols('T23', 'ID', 222, 10)
T23::10 found in COL_A,,,

PL/SQL procedure successfully completed.

SQL>

- APC

---

是的，我知道这种东西肯定存在。谢谢！ - David Oneill

Answer 2

我的解决方案将使用字典表（USER_TAB_COLUMNS）动态获取您的表中所有NUMBER列的名称，并使用动态SQL，因为我认为这里无法避免它。

DECLARE
   CURSOR cur_columns IS
          select COLUMN_NAME from USER_TAB_COLUMNS 
          where TABLE_NAME='<MY_TABLE>' and DATA_TYPE='NUMBER';
   query_text VARCHAR2(1000);
   result_value NUMBER;
BEGIN
   -- Iterate through each NUMBER column of the table
   FOR rec_col IN cur_columns LOOP

       -- In my line of primary key <MY_ID>, check if the current column has
       --  the wanted value.
       query_text := 
         'SELECT count(1) FROM <MY_TABLE> WHERE <TABLE_ID> = <MY_ID> AND ' 
         || rec_col.COLUMN_NAME || ' = <MY_VALUE>'; -- < the "magic" is here

       EXECUTE IMMEDIATE query_text INTO result_value; 

       IF result_value > 0 THEN
          DBMS_OUTPUT.PUT_LINE('Got a match for column ' || 
                               rec_col.COLUMN_NAME || '.');
       END IF;

   END LOOP;
END;

显然，您需要用所选值替换所有<变量>。

对于手动查询，它可以正常工作。但是，这种方法的性能可能很差，因此不要直接运行它来处理大量数据集。

- Manur

---

是的，我知道这种东西肯定存在。谢谢！ - David Oneill

Answer 3

听起来你的数据库没有正确规范化。话虽如此，你可能可以在子查询中使用UNPIVOT命令来实现你想要做的事情。

- Tom H

---

你能提供更多有关“unpivot”的细节吗？具体而言，“unpivot”在Oracle中是否可行？ - David Oneill

@David Oneill：UNPIVOT（以及PIVOT）仅在Oracle 11g+中受支持。任何早于此版本的数据库都需要使用CASE或DECODE语句 - 可以在SO上检查sql、pivot和/或ranking标签。 - OMG Ponies