select e.* from basicdata_employee e minus (select e.* from basicdata_employee e where rownum < 14 minus select e.* from basicdata_employee e where rownum < 13)
没有这样的偷懒方法,如果你懒得按字段写代码的话,你可以用存储过程去构建你的SQL语句!
或者 select * from( select rownum num,e.cname cname from basicdata_employee e)a where a.num != 13
create or replace procedure sql_create( i_tbname varchar2, -- 默认大写(可以写成小写,但我会将其转换成大小去比较,这里可以按照你的需求去更改) i_except_columns varchar2, -- 要排除的字段名,多个字段用逗号(,)隔开(因为Oracle里面的字段默认均为大写,所以输入时要注意,当然可以用upper()函数,那是后话) o_sql out varchar2 ) is v_sqls varchar2(4000); i_except_columns2 varchar2(4000); v_sql varchar2(4000); v_cnt number(18,0); begin v_sql := ''; i_except_columns2 :=''''||replace(upper(i_except_columns),',',''',''')||''''; execute immediate 'select count(1) from user_tables where table_name=upper(:i_tbname)' into v_cnt using i_tbname; -- 看是否存在大写名字的表 dbms_output.put_line(to_char(v_cnt)); if v_cnt = 1 then begin v_sqls := 'select trim(regexp_replace(wm_concat((case when m.column_name<>upper(m.column_name) then '' "''||m.column_name||''"'' else '' ''||m.column_name end)),'','','',''||chr(10))) as columnNames from user_tab_columns m where m.table_name=upper(:i_tbname) and m.column_name not in ('||i_except_columns2||') order by column_id asc'; execute immediate v_sqls into v_sql using i_tbname; o_sql := 'SELECT '||v_sql||chr(10)||' FROM '||i_tbname||';'; end; else begin execute immediate 'select count(1) from user_tables where table_name=:i_tbname' into v_cnt using i_tbname; -- 看是否存在小写名字的表 if v_cnt = 1 then begin v_sqls := 'select trim(regexp_replace(wm_concat((case when m.column_name<>upper(m.column_name) then '' "''||m.column_name||''"'' else '' ''||m.column_name end)),'','','',''||chr(10))) as columnNames from user_tab_columns m where m.table_name=:i_tbname and m.column_name not in ('||i_except_columns2||') order by column_id asc'; execute immediate v_sqls into v_sql using i_tbname; o_sql := 'SELECT '||v_sql||chr(10)||' FROM '||'"'||i_tbname||'"'; end; else o_sql := '对不起,表名为 '||i_tbname||'、'||upper(i_tbname)||' 在当前用户 '||user||' 下均不存在! '; end if; end; end if; end; /-- select dbms_metadata.get_ddl('TABLE','MUSICMUSIC') from dual;set serveroutput on; var sqls varchar2(4000); exec sql_create('musicmusic','musicid',:sqls); print sqls;
-- 要排除第几列的话,可以用column_id去构建你的代码(方法同理,不再不再赘述)
我客户端用的是 toad,遇到这种情况,先随便选出一行,然后 右键-save as -insert statement to clipboard。 然后所有的列都出来了。
有,用个子查询,你看能不能先通过分析函数对你的数据通过某一个字段进行排序得到你想要的那种顺序,然后在通过一个子查询就应该可以取到你要的数据了,代码如下:看是不是你想要的.SELECT * FROM ( SELECT ENAME,JOB,SAL,DEPTNO,ROW_NUMBER() OVER(ORDER BY SAL DESC) RANK_R FROM EMP ) WHERE RANK_R BETWEEN 1 AND 4 ;
--查询tablename表中第10行到第20行之间的记录 select * from (select rownum r,a.* from tablename where rownum<=20) where r>=10;
select * from (select rownum r,a.* from 表名 a where rownum<=20) where r>=1 and r!=13;
from basicdata_employee e
minus
(select e.*
from basicdata_employee e
where rownum < 14
minus
select e.*
from basicdata_employee e
where rownum < 13)
select *
from(
select rownum num,e.cname cname
from basicdata_employee e)a
where a.num != 13
i_tbname varchar2, -- 默认大写(可以写成小写,但我会将其转换成大小去比较,这里可以按照你的需求去更改)
i_except_columns varchar2, -- 要排除的字段名,多个字段用逗号(,)隔开(因为Oracle里面的字段默认均为大写,所以输入时要注意,当然可以用upper()函数,那是后话)
o_sql out varchar2
)
is
v_sqls varchar2(4000);
i_except_columns2 varchar2(4000);
v_sql varchar2(4000);
v_cnt number(18,0);
begin
v_sql := '';
i_except_columns2 :=''''||replace(upper(i_except_columns),',',''',''')||'''';
execute immediate 'select count(1) from user_tables where table_name=upper(:i_tbname)' into v_cnt using i_tbname; -- 看是否存在大写名字的表
dbms_output.put_line(to_char(v_cnt));
if v_cnt = 1 then
begin
v_sqls := 'select trim(regexp_replace(wm_concat((case when m.column_name<>upper(m.column_name) then '' "''||m.column_name||''"'' else '' ''||m.column_name end)),'','','',''||chr(10))) as columnNames
from user_tab_columns m
where m.table_name=upper(:i_tbname)
and m.column_name not in ('||i_except_columns2||')
order by column_id asc';
execute immediate v_sqls into v_sql using i_tbname;
o_sql := 'SELECT '||v_sql||chr(10)||' FROM '||i_tbname||';';
end;
else
begin
execute immediate 'select count(1) from user_tables where table_name=:i_tbname' into v_cnt using i_tbname; -- 看是否存在小写名字的表
if v_cnt = 1 then
begin
v_sqls := 'select trim(regexp_replace(wm_concat((case when m.column_name<>upper(m.column_name) then '' "''||m.column_name||''"'' else '' ''||m.column_name end)),'','','',''||chr(10))) as columnNames
from user_tab_columns m
where m.table_name=:i_tbname
and m.column_name not in ('||i_except_columns2||')
order by column_id asc';
execute immediate v_sqls into v_sql using i_tbname; o_sql := 'SELECT '||v_sql||chr(10)||' FROM '||'"'||i_tbname||'"';
end;
else
o_sql := '对不起,表名为 '||i_tbname||'、'||upper(i_tbname)||' 在当前用户 '||user||' 下均不存在! ';
end if;
end;
end if;
end;
/-- select dbms_metadata.get_ddl('TABLE','MUSICMUSIC') from dual;set serveroutput on;
var sqls varchar2(4000);
exec sql_create('musicmusic','musicid',:sqls);
print sqls;
-- 要排除第几列的话,可以用column_id去构建你的代码(方法同理,不再不再赘述)
然后所有的列都出来了。
SELECT ENAME,JOB,SAL,DEPTNO,ROW_NUMBER() OVER(ORDER BY SAL DESC) RANK_R FROM EMP
) WHERE RANK_R BETWEEN 1 AND 4 ;
select * from (select rownum r,a.* from tablename where rownum<=20) where r>=10;