实在懒就这样吧,删掉你不要的字段名就行了SELECT MAX (SUBSTR (SYS_CONNECT_BY_PATH (colb, ','), 2)) colb FROM (SELECT colb, colc, LEAD (colc) OVER (ORDER BY colc) cold FROM (SELECT colb, ROW_NUMBER () OVER (ORDER BY colb) colc FROM (SELECT cname AS colb FROM col WHERE tname = UPPER ('表名')))) START WITH cold IS NULL CONNECT BY PRIOR colc = cold
我是用PB+ORACLE编程的,像你那种情况,可以在PB中通过数据窗口获取SQL语法解决!
有道理,通过PB数据窗口生成的SQL语句,去掉你不要的字段就可以了
最简单办法: select zd.cname from yourtable, (select cname from col where tname=upper('yourtable') and cname !=upper('不要的字段')) zd
笨方便!!! desc table后,拷贝字段名,去掉不要的字段,然后拷到select 中
SQL> --全部的字段 SQL> select column_name from user_tab_columns where table_name=upper('henry_cailing');COLUMN_NAME ------------------------------ NET_ID SP_ID BAD_CHARGE_AFTER BAD_CHARGE_FIRST ADJUST_CHARGE_AFTER ADJUST_CHARGE_FIRST TOTAL_ADJUST_CHARGE ADJUST_DESCRIPTION BILLING_MONTH9 rows selectedSQL> --去掉billing_month SQL> select column_name from user_tab_columns where table_name=upper('henry_cailing') and column_name<>upper('billing_month');COLUMN_NAME ------------------------------ NET_ID SP_ID BAD_CHARGE_AFTER BAD_CHARGE_FIRST ADJUST_CHARGE_AFTER ADJUST_CHARGE_FIRST TOTAL_ADJUST_CHARGE ADJUST_DESCRIPTION8 rows selectedSQL> set serveroutput on; SQL> declare 2 cursor m_cur is 3 select column_name from user_tab_columns where table_name=upper('henry_cailing') and column_name<>upper('billing_month'); 4 v_sqlstr varchar2(4000):=' '; 5 v_col_name varchar2(50); 6 begin 7 open m_cur; 8 loop 9 fetch m_cur into v_col_name; 10 exit when m_cur%notfound; 11 v_sqlstr:=v_sqlstr||v_col_name||','; 12 end loop; 13 if length(v_sqlstr)>1 then 14 v_sqlstr:=substr(v_sqlstr,1,length(v_sqlstr)-1); 15 v_sqlstr:='select '||v_sqlstr||' from henry_cailing'; 16 end if; 17 dbms_output.put_line(v_sqlstr); 18 end; 19 /select NET_ID,SP_ID,BAD_CHARGE_AFTER,BAD_CHARGE_FIRST,ADJUST_CHARGE_AFTER,ADJUST_CHARGE_FIRST,TOTAL_ADJUST_CHARGE,ADJUST_DESCRIPTION from henry_cailingPL/SQL procedure successfully completed
你要是经常要select这69个字段的话,就建立一个视图吧,视图里只要这69个字段,这样就可以用 select * from 视图 了:)多方便啊:)
FROM (SELECT colb, colc, LEAD (colc) OVER (ORDER BY colc) cold
FROM (SELECT colb, ROW_NUMBER () OVER (ORDER BY colb) colc
FROM (SELECT cname AS colb
FROM col
WHERE tname = UPPER ('表名'))))
START WITH cold IS NULL
CONNECT BY PRIOR colc = cold
select zd.cname
from yourtable,
(select cname from col where tname=upper('yourtable') and cname !=upper('不要的字段'))
zd
desc table后,拷贝字段名,去掉不要的字段,然后拷到select
中
SQL> select column_name from user_tab_columns where table_name=upper('henry_cailing');COLUMN_NAME
------------------------------
NET_ID
SP_ID
BAD_CHARGE_AFTER
BAD_CHARGE_FIRST
ADJUST_CHARGE_AFTER
ADJUST_CHARGE_FIRST
TOTAL_ADJUST_CHARGE
ADJUST_DESCRIPTION
BILLING_MONTH9 rows selectedSQL> --去掉billing_month
SQL> select column_name from user_tab_columns where table_name=upper('henry_cailing') and column_name<>upper('billing_month');COLUMN_NAME
------------------------------
NET_ID
SP_ID
BAD_CHARGE_AFTER
BAD_CHARGE_FIRST
ADJUST_CHARGE_AFTER
ADJUST_CHARGE_FIRST
TOTAL_ADJUST_CHARGE
ADJUST_DESCRIPTION8 rows selectedSQL> set serveroutput on;
SQL> declare
2 cursor m_cur is
3 select column_name from user_tab_columns where table_name=upper('henry_cailing') and column_name<>upper('billing_month');
4 v_sqlstr varchar2(4000):=' ';
5 v_col_name varchar2(50);
6 begin
7 open m_cur;
8 loop
9 fetch m_cur into v_col_name;
10 exit when m_cur%notfound;
11 v_sqlstr:=v_sqlstr||v_col_name||',';
12 end loop;
13 if length(v_sqlstr)>1 then
14 v_sqlstr:=substr(v_sqlstr,1,length(v_sqlstr)-1);
15 v_sqlstr:='select '||v_sqlstr||' from henry_cailing';
16 end if;
17 dbms_output.put_line(v_sqlstr);
18 end;
19 /select NET_ID,SP_ID,BAD_CHARGE_AFTER,BAD_CHARGE_FIRST,ADJUST_CHARGE_AFTER,ADJUST_CHARGE_FIRST,TOTAL_ADJUST_CHARGE,ADJUST_DESCRIPTION from henry_cailingPL/SQL procedure successfully completed
select * from 视图 了:)多方便啊:)