with tt as(select 1 id,1 pid,20 suba,30 subb,30 subc,15 subd,35 sube from dual union all select 2,2,10,24,56,0,80 from dual)
select id,pid,colname from( select id,pid,colname, row_number()over(partition by id,pid order by value desc)rn from( select id,pid,suba value,'suba'colname from tt union all select id,pid,subb,'subb' from tt union all select id,pid,subc,'subc' from tt union all select id,pid,subd,'subd' from tt union all select id,pid,sube,'sube' from tt) )where rn=1
create table row_to_col as select 1 id,2 name from dual union all select 1 id ,2 name from dual union all select 20 id,10 name from dual union all select 30 id,24 name from dual union all select 30 id ,56 name from dual union all select 15 id, 0 name from dual union all select 35 id,80 name from dual;
select max(id),max(name) from row_to_col
找最大值,可以用GREATEST函数,但同时要想得到列名,就麻烦点,需要用楼上几位的方法
--建表 CREATE TABLE test (ID NUMBER,pid NUMBER,suba NUMBER,subB NUMBER,subC NUMBER,subD NUMBER,subE NUMBER) ; --插入数据 INSERT INTO test VALUES( 1 ,1 , 20, 30, 30 , 15 , 35 ); INSERT INTO test VALUES( 2 , 2 , 10, 24 , 56 , 0 , 80 ); --得出结果 SELECT id, pid, greatest(suba, subb, subc, subd, sube) max_grade, CASE WHEN greatest(suba, subb, subc, subd, sube) - suba = 0 THEN 'subA' WHEN greatest(suba, subb, subc, subd, sube) - subb = 0 THEN 'subB' WHEN greatest(suba, subb, subc, subd, sube) - subc = 0 THEN 'subC' WHEN greatest(suba, subb, subc, subd, sube) - subd = 0 THEN 'subD' WHEN greatest(suba, subb, subc, subd, sube) - sube = 0 THEN 'subE' END max_sub FROM test;
[TEST@ora10gr1#2009-11-25/20:54:54] SQL>create table tt(id int, pid int, subA int, subB int, subC int, subD int, subE int);Table created.[TEST@ora10gr1#2009-11-25/20:54:54] SQL>insert into tt values(1,1,20,30,30,15,35);1 row created.[TEST@ora10gr1#2009-11-25/20:54:54] SQL>insert into tt values(2,2,10,24,56,0 ,80);1 row created.[TEST@ora10gr1#2009-11-25/20:54:54] SQL>set serveroutput on; [TEST@ora10gr1#2009-11-25/20:54:54] SQL>declare 2 type tt_table_type is table of tt%rowtype; 3 tt_table tt_table_type; 4 sql_str varchar(3000) :=' '; 5 max_value number :=0; 6 begin 7 select * bulk collect into tt_table from tt; 8 for i in 1..tt_table.count loop 9 sql_str :=' select greatest('||tt_table(i).subA||','||tt_table(i).subB||','||tt_table(i).subC||','||tt_table(i).subD||','||tt_table(i).subE||') from dual'; 10 execute immediate sql_str into max_value; 11 if tt_table(i).subA = max_value then 12 dbms_output.put_line('id=['||tt_table(i).id||'] and pid=['||tt_table(i).id||'] 行的最大值列为:[subA],最大值为:['||max_value||']'); 13 end if; 14 if tt_table(i).subB = max_value then 15 dbms_output.put_line('id=['||tt_table(i).id||'] and pid=['||tt_table(i).id||'] 行的最大值列为:[subB],最大值为:['||max_value||']'); 16 end if; 17 if tt_table(i).subC = max_value then 18 dbms_output.put_line('id=['||tt_table(i).id||'] and pid=['||tt_table(i).id||'] 行的最大值列为:[subC],最大值为:['||max_value||']'); 19 end if; 20 if tt_table(i).subD = max_value then 21 dbms_output.put_line('id=['||tt_table(i).id||'] and pid=['||tt_table(i).id||'] 行的最大值列为:[subD],最大值为:['||max_value||']'); 22 end if; 23 if tt_table(i).subE = max_value then 24 dbms_output.put_line('id=['||tt_table(i).id||'] and pid=['||tt_table(i).id||'] 行的最大值列为:[subE],最大值为:['||max_value||']'); 25 end if; 26 end loop; 27 end; 28 / id=[1] and pid=[1] 行的最大值列为:[subE],最大值为:[35] id=[2] and pid=[2] 行的最大值列为:[subE],最大值为:[80]PL/SQL procedure successfully completed.
union all select 2,2,10,24,56,0,80 from dual)
select id,pid,colname from(
select id,pid,colname,
row_number()over(partition by id,pid order by value desc)rn from(
select id,pid,suba value,'suba'colname from tt
union all
select id,pid,subb,'subb' from tt
union all
select id,pid,subc,'subc' from tt
union all
select id,pid,subd,'subd' from tt
union all
select id,pid,sube,'sube' from tt)
)where rn=1
select 1 id,2 name from dual
union all
select 1 id ,2 name from dual
union all
select 20 id,10 name from dual
union all
select 30 id,24 name from dual
union all
select 30 id ,56 name from dual
union all
select 15 id, 0 name from dual
union all
select 35 id,80 name from dual;
select max(id),max(name) from row_to_col
CREATE TABLE test (ID NUMBER,pid NUMBER,suba NUMBER,subB NUMBER,subC NUMBER,subD NUMBER,subE NUMBER) ;
--插入数据
INSERT INTO test VALUES( 1 ,1 , 20, 30, 30 , 15 , 35 );
INSERT INTO test VALUES( 2 , 2 , 10, 24 , 56 , 0 , 80 );
--得出结果
SELECT id,
pid,
greatest(suba, subb, subc, subd, sube) max_grade,
CASE
WHEN greatest(suba, subb, subc, subd, sube) - suba = 0 THEN
'subA'
WHEN greatest(suba, subb, subc, subd, sube) - subb = 0 THEN
'subB'
WHEN greatest(suba, subb, subc, subd, sube) - subc = 0 THEN
'subC'
WHEN greatest(suba, subb, subc, subd, sube) - subd = 0 THEN
'subD'
WHEN greatest(suba, subb, subc, subd, sube) - sube = 0 THEN
'subE'
END max_sub
FROM test;
[TEST@ora10gr1#2009-11-25/20:54:54] SQL>create table tt(id int, pid int, subA int, subB int, subC int, subD int, subE int);Table created.[TEST@ora10gr1#2009-11-25/20:54:54] SQL>insert into tt values(1,1,20,30,30,15,35);1 row created.[TEST@ora10gr1#2009-11-25/20:54:54] SQL>insert into tt values(2,2,10,24,56,0 ,80);1 row created.[TEST@ora10gr1#2009-11-25/20:54:54] SQL>set serveroutput on;
[TEST@ora10gr1#2009-11-25/20:54:54] SQL>declare
2 type tt_table_type is table of tt%rowtype;
3 tt_table tt_table_type;
4 sql_str varchar(3000) :=' ';
5 max_value number :=0;
6 begin
7 select * bulk collect into tt_table from tt;
8 for i in 1..tt_table.count loop
9 sql_str :=' select greatest('||tt_table(i).subA||','||tt_table(i).subB||','||tt_table(i).subC||','||tt_table(i).subD||','||tt_table(i).subE||') from dual';
10 execute immediate sql_str into max_value;
11 if tt_table(i).subA = max_value then
12 dbms_output.put_line('id=['||tt_table(i).id||'] and pid=['||tt_table(i).id||'] 行的最大值列为:[subA],最大值为:['||max_value||']');
13 end if;
14 if tt_table(i).subB = max_value then
15 dbms_output.put_line('id=['||tt_table(i).id||'] and pid=['||tt_table(i).id||'] 行的最大值列为:[subB],最大值为:['||max_value||']');
16 end if;
17 if tt_table(i).subC = max_value then
18 dbms_output.put_line('id=['||tt_table(i).id||'] and pid=['||tt_table(i).id||'] 行的最大值列为:[subC],最大值为:['||max_value||']');
19 end if;
20 if tt_table(i).subD = max_value then
21 dbms_output.put_line('id=['||tt_table(i).id||'] and pid=['||tt_table(i).id||'] 行的最大值列为:[subD],最大值为:['||max_value||']');
22 end if;
23 if tt_table(i).subE = max_value then
24 dbms_output.put_line('id=['||tt_table(i).id||'] and pid=['||tt_table(i).id||'] 行的最大值列为:[subE],最大值为:['||max_value||']');
25 end if;
26 end loop;
27 end;
28 /
id=[1] and pid=[1] 行的最大值列为:[subE],最大值为:[35]
id=[2] and pid=[2] 行的最大值列为:[subE],最大值为:[80]PL/SQL procedure successfully completed.