可以这样 select column_name||',' from user_tab_columns where table_name='tablename' and column_name<>'不要期中的一个字段';
曾经看过一个很经典的获取字段并查询的,不过不记得了 刚才查资料,没有搞定。select substr(c,2) as c1 from (Select max(SYS_CONNECT_BY_PATH(COLUMN_NAME, ',')) as c From (select A.COLUMN_NAME,A.TABLE_NAME, ROWNUM AS ROWNO from USER_TAB_COLUMNS A where TABLE_NAME = 'T2' ORDER BY A.COLUMN_ID) start with ROWNO = 1 connect by ROWNO = rownum) t上面的代码可以获取T2表的字段清单并构成C1,C2,C3的这种格式。但还是没实现。
给lz一个参考用动态sql SQL> select COLUMN_NAME from USER_TAB_COLUMNS where table_name='MAIN_TABLE';COLUMN_NAME ------------------------------ UUID ORGN_CODE ORGN_NAME ORGN_NUMBER SPEC_CODE ORGN_TYPE_CODE PAR_ORGN_CODE STAT_UNIT_CODE UUID_TIMESTAMP STATUS 假定你不要显示uuid select COLUMN_NAME from USER_TAB_COLUMNS where table_name='MAIN_TABLE' and COLUMN_NAME <>'UUID'; select 'select ' a from dual union all select COLUMN_NAME||',' a from USER_TAB_COLUMNS where table_name='MAIN_TABLE' and COLUMN_NAME <>'UUID' union all select ' from main_table' a from dual;
不好意思啊 我在做的是分页 SELECT *FROM
(SELECT A.*,rownum rn FROM (select * from test) A WHERE ROWNUM <= 10)
楼主的查询语句: SELECT * FROM (SELECT A.*,rownum rn FROM (select * from test) A WHERE ROWNUM <= 10) WHERE rn >= 5; 等同于 SELECT * FROM TEST WHERE ROWNUM >=5 AND ROWNUM <=10;如果通过简化你的sql,不就可以去掉rn了吗?
SELECT * FROM TEST WHERE ROWNUM >=5 AND ROWNUM <=10; 这个语句是不可能查询成功的 你试试 rownum不能使用>= 做查询的
你倒是突然提醒了我,是的,rownum不能between的。你可以使用minus语法SELECT * FROM TEST WHERE ROWNUM <=10minusSELECT * FROM TEST WHERE ROWNUM <=5;就可以取到6-10行了
你弄个1万条数据试试,insert into test select * from test,多复制几次,或者自己编个PLSQL段,随便你,然后你打开统计一下你执行刚才的SQL所需的时间,比较一下不同数据不同SQL是所用的时间,具体怎么用,网上很多,我记得tom的那本expert one on one里面就有。
select column_name||',' from user_tab_columns where table_name='tablename' and column_name<>'不要期中的一个字段';
刚才查资料,没有搞定。select substr(c,2) as c1 from (Select max(SYS_CONNECT_BY_PATH(COLUMN_NAME, ',')) as c
From (select A.COLUMN_NAME,A.TABLE_NAME, ROWNUM AS ROWNO
from USER_TAB_COLUMNS A
where TABLE_NAME = 'T2'
ORDER BY A.COLUMN_ID)
start with ROWNO = 1
connect by ROWNO = rownum) t上面的代码可以获取T2表的字段清单并构成C1,C2,C3的这种格式。但还是没实现。
SQL> select COLUMN_NAME from USER_TAB_COLUMNS where table_name='MAIN_TABLE';COLUMN_NAME
------------------------------
UUID
ORGN_CODE
ORGN_NAME
ORGN_NUMBER
SPEC_CODE
ORGN_TYPE_CODE
PAR_ORGN_CODE
STAT_UNIT_CODE
UUID_TIMESTAMP
STATUS
假定你不要显示uuid
select COLUMN_NAME from USER_TAB_COLUMNS where table_name='MAIN_TABLE' and COLUMN_NAME <>'UUID';
select 'select ' a from dual
union all
select COLUMN_NAME||',' a from USER_TAB_COLUMNS where table_name='MAIN_TABLE' and COLUMN_NAME <>'UUID'
union all
select ' from main_table' a from dual;
我在做的是分页
SELECT *FROM
(SELECT A.*,rownum rn FROM (select * from test) A WHERE ROWNUM <= 10)
WHERE rn >= 5;
我不想要 rn 就是rownum 这个字段 谢谢
你要查3个字段,非要只显示2个,oracle怎么会有这样的功能
SELECT * FROM
(SELECT A.*,rownum rn FROM (select * from test) A WHERE ROWNUM <= 10)
WHERE rn >= 5;
等同于
SELECT * FROM TEST WHERE ROWNUM >=5 AND ROWNUM <=10;如果通过简化你的sql,不就可以去掉rn了吗?
这个语句是不可能查询成功的 你试试 rownum不能使用>= 做查询的