--我创建了一个表 TEST2
create or replace procedure P_TEST is
C_NAME VARCHAR2(20);
v_sql varchar2(300);
v_max number;
begin
DECLARE
CURSOR cu1 IS
select column_name from user_TAB_COLUMNS where table_name='TEST2';
begin
OPEN cu1;
fetch cu1 INTO C_NAME;
WHILE cu1%FOUND LOOP
if v_sql is null then
v_sql:=' select ' || C_NAME ||' as c from test2 ' ;
else
v_sql:= v_sql || ' union all select ' || C_NAME ||' as c from test2 ';
end if;
FETCH cu1 INTO C_NAME;
END LOOP;
CLOSE cu1;
dbms_output.put_line(v_sql);
if v_sql is not null then
execute immediate 'select max(count) from (select c,count(*) as count from (' || v_sql || ') group by c )' into v_max ;
dbms_output.put_line(v_max);
end if;
end;
end P_TEST;
create or replace procedure P_TEST is
C_NAME VARCHAR2(20);
v_sql varchar2(300);
v_max number;
begin
DECLARE
CURSOR cu1 IS
select column_name from user_TAB_COLUMNS where table_name='TEST2';
begin
OPEN cu1;
fetch cu1 INTO C_NAME;
WHILE cu1%FOUND LOOP
if v_sql is null then
v_sql:=' select ' || C_NAME ||' as c from test2 ' ;
else
v_sql:= v_sql || ' union all select ' || C_NAME ||' as c from test2 ';
end if;
FETCH cu1 INTO C_NAME;
END LOOP;
CLOSE cu1;
dbms_output.put_line(v_sql);
if v_sql is not null then
execute immediate 'select max(count) from (select c,count(*) as count from (' || v_sql || ') group by c )' into v_max ;
dbms_output.put_line(v_max);
end if;
end;
end P_TEST;
多谢!现在还有个问题,假如test2这个表里面不止这30个field的话该怎么办?因为现实里这个表有100多个field!(-_-!).还有不用存储过程能不能实现?
select column_name from user_TAB_COLUMNS where table_name='TEST2'
这条语句会把所有的表的字段遍历出来。所以只要在ORACLE允许的字段数(<=1024),都可以实现;如果不用过程。那你手写SQL语句?
select max(count) from (
select c,count(*) as count from (
select 字段1 as c from 表
union all
select 字段2 as c from 表
union all
select 字段3 as c from 表
.......
) group by c
)
select c,count(*) as count
这里面的c是什么意思?