--只要D. select d from (你的查询) t where d = 4--显示全部 select * from (你的查询) t where d = 4
赫赫,case就是了。 返回的d,不就是4嘛?select ( CASE WHEN a=4 THEN 'a' WHEN b=4 THEN 'b' WHEN c=4 THEN 'c' WHEN d=4 THEN 'd' ELSE null END ) F1, 4 F2 from t从这个结果里面,判断字段F1不是null就可以了。不是null的,就是字段名字了。
SQL> create table t(a int,b int,c int,d int);Table createdSQL> insert into t select 1,2,3,4 from dual;1 row insertedSQL> create or replace function getfldname(value int) 2 return nvarchar2 3 as 4 fldname nvarchar2(30); 5 value1 int; 6 begin 7 fldname := ''; 8 for rec in (select column_name from user_tab_columns where table_name='T') 9 loop 10 begin 11 execute immediate 'select '||rec.column_name||' from t where '||rec.column_name||' =' ||value into value1; 12 Exception 13 WHEN NO_DATA_FOUND THEN 14 fldname := ''; 15 end; 16 if (value1 = value) then 17 fldname := rec.column_name; 18 exit; 19 end if; 20 end loop; 21 return fldname; 22 end; 23 /Function createdSQL> --dbms_output.put_line('select '||rec.column_name||' from t where '||rec.column_name||' = 4'); SQL> declare 2 a varchar2(30); 3 begin 4 a := getfldname(1); 5 dbms_output.put_line(a); 6 a := getfldname(2); 7 dbms_output.put_line(a); 8 a := getfldname(3); 9 dbms_output.put_line(a); 10 a := getfldname(4); 11 dbms_output.put_line(a); 12 end; 13 /A B C DPL/SQL procedure successfully completed
select d from
(你的查询) t
where d = 4--显示全部
select * from
(你的查询) t
where d = 4
返回的d,不就是4嘛?select ( CASE
WHEN a=4 THEN 'a'
WHEN b=4 THEN 'b'
WHEN c=4 THEN 'c'
WHEN d=4 THEN 'd'
ELSE null
END ) F1, 4 F2
from t从这个结果里面,判断字段F1不是null就可以了。不是null的,就是字段名字了。
SQL> create table t(a int,b int,c int,d int);Table createdSQL> insert into t select 1,2,3,4 from dual;1 row insertedSQL> create or replace function getfldname(value int)
2 return nvarchar2
3 as
4 fldname nvarchar2(30);
5 value1 int;
6 begin
7 fldname := '';
8 for rec in (select column_name from user_tab_columns where table_name='T')
9 loop
10 begin
11 execute immediate 'select '||rec.column_name||' from t where '||rec.column_name||' =' ||value into value1;
12 Exception
13 WHEN NO_DATA_FOUND THEN
14 fldname := '';
15 end;
16 if (value1 = value) then
17 fldname := rec.column_name;
18 exit;
19 end if;
20 end loop;
21 return fldname;
22 end;
23 /Function createdSQL> --dbms_output.put_line('select '||rec.column_name||' from t where '||rec.column_name||' = 4');
SQL> declare
2 a varchar2(30);
3 begin
4 a := getfldname(1);
5 dbms_output.put_line(a);
6 a := getfldname(2);
7 dbms_output.put_line(a);
8 a := getfldname(3);
9 dbms_output.put_line(a);
10 a := getfldname(4);
11 dbms_output.put_line(a);
12 end;
13 /A
B
C
DPL/SQL procedure successfully completed