Order by 的数值型灵活使用

2019-11-17 05:38:39

字体：大中小

来源：转载

供稿：网友

　　代码:
select * from table_a where id=p_id order by decode(函数,'asc',1,'desc',-1)*jsny;

控制试图的访问时间：
6. 代码:
create view ...
as
select ... from where exists(select x from dual where sysdate>=8:00am and sysdate<=5:00pm)

妙用decode实现排序
代码:
select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');
select * from tabname
order by decode(mode,'FIFO',rq-sysdate, sysdate-rq)
找出某个时期内工作日数：
代码:
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1 )
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) not
in ( '1', '7' )

我觉得查询重复记录的语句就很经典
代码:
select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd)
由它引申的就有很多有用的语句，如昨天回答别人的排序的难题
代码:
select id,bdsszd from BADWDJ a where a.id = (select max(id) from BADWDJ b where a.bdsszd =b.bdsszd) order by id

树型结构表的查询：
代码:
select ID,PARENT_ID from parent_child
connect by PRior id = parent_id
start with id = 1;

1.decode这个函数一定需要会，我觉得sql的灵活很多地方都是通过这个function来体现的，相当于if，很好用。
2.group by，这个东东想想简单，其实好多统计功能是离不开这个操作的。Oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。值得注重的是，当你对事物做过有效的人为归并之后执行group by 往往会更让人心旷神怡。
3.很表竖置的经典写法，也要记住：代码:
sum(decode( )) group by ...
注重：需要在一个subquery中确定一个横置判点。
4.树形结构表的遍历写法：代码:
select ...from ....
start with ... connect by prior
(父子关系表达式)
代码:
select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var);

816以上的一些分析函数如 rank() over() and row_number() over()
当然关于 group by rollup/cube使用的人恐怕非凡少
如何实现最大匹配的操作?
例如:给定字符串 '1234', 而表中可能有记录项'1','12','123','1234','12345',要选出'1234'项
代码:
select * from (
select col_FT from table_FT
where instr('12345',col_FT)=1
order by length(col_FT) desc)
where rownum =1

给你一个意想不到的东西

代码:
SQL> select to_char(to_date(12,'yyyy'),'year') from dual;
TO_CHAR(TO_DATE(12,'YYYY'),'YEAR')
------------------------------------------
twelve
select to_char(sysdate,'day') from dual
还有 d、iw、mm等等格式
对于translate函数有一个功能
比如：找出某个字符串中完全是数字
代码:
select * from xxx where translate(column1,'1234567890','') = column1;
select trunc(sysdate) from dual;

select trunc(sysdate,'mm') from dual;
大家构造几个例子看看就能明白
代码:
select a,b,sum(c) from xxx group by rollup(a,b);
select a,b,sum(c) from xxx group by cube(a,b);

怎么查找字符串里面包含有%的记录：
当然，常规方法就是利用 escape了
可假如不知道escape也行，比如
代码:
select * from xxx where replace(a,'%','') = a;

利用decode解决动态sql的不确定条件查询的问题:
假设前台传入的都是变量
代码:
select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var);
这样比 like :var'%' 效率高
另:对于
代码:
select ...
from a,b
where a.a = b.a(+) and b.a is null;
我想对于不明白的朋友,我要交代一下用处才好:
比如,你需要查找在a表中有而b表中没有的记录
也许你会选择 not in:
select * from a aa where aa.a1 not in (select a1 from bb);
这是效率最低的
或者:
select a1 from aa
minus
select a1 from bb;
所有这些写法,都不如下面下率高:
代码:
select a.* from aa a,bb b
where a.a1 = b.a1(+) and b.a1 is null;
给一个很普通的适用的最高效的外连接例子（不是什么新鲜玩意）：
select ...
from a,b
where a.a = b.a(+) and b.a is null;
我要按年龄段（小于20，20-30，---）统计人数，我可以用
代码:
select
sum(decode(sign(age - 20),-1,1,0)),
sum(decode(sign(age - 20),-1,0,(decode(sign(age - 30,-1,1,0))))),
sum(decode(sign(age - 30),-1,0,(decode(sign(age - 40,-1,1,0))))),
sum(decode(sign(age - 40),-1,0,(decode(sign(age - 50,-1,1,0))))),
sum(decode(sign(age - 50),-1,0,1))
from xxx;
这样只做一遍表扫描
这是分了20以下和50以上的
类似的问题，自己扩展了
添加行号：
代码:
select (select count(*) from a1 where item <= a.item) AS ROW, * FROM a1 as a order by item
select * from table1 a
where id in (select top 3 from table1 where 物品=a.物品 order by price desc)

每一种物品有很多价格，每一种物品选择排在前三的纪录
1。job的使用：
代码:
DBMS_JOB.SUBM99v(:jobno,//job号
'your_procedure;',//要执行的过程
trunc(sysdate)+1/24,//下次执行时间
'trunc(sysdate)+1/24+1'//每次间隔时间
);
删除job:dbms_job.remove(jobno);
修改要执行的操作:job:dbms_job.what(jobno,what);
修改下次执行时间：dbms_job.next_date(job,next_date);
修改间隔时间：dbms_job.interval(job,interval);
停止job:dbms.broken(job,broken,nextdate);
启动job:dbms_job.run(jobno);
例子：
代码:
VARIABLE jobno number;
begin
DBMS_JOB.SUBM99v(:jobno,
'Procdemo;',//Procdemo为过程名称
SYSDATE, 'SYSDATE + 1/720');
commit;
end;
/
2。把一个表放在内存里
alter table tablename cache.
3。创建临时表
代码:
CREATE GLOBAL TEMPORARY TABLE TABLENAME (
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMM99v PRESERVE(DELETE) ROWS ;
这种临时表不占用表空间，而且不同的session之间互相看不到对方的数据
在会话结束后表中的数据自动清空，假如选了DELETE ROWS，则在提交的时候即清

空数据，PRESERVE则一直到会话结束
4。加一个自动增加的id号
第一种方法：
第一步：创建SEQUENCE
代码:
create sequence s_country_id increment by 1 start with 1 maxvalue

999999999;
第二步：创建一个基于该表的before insert 触发器，在触发器中使用该
代码:
SEQUENCE
create or replace trigger bef_ins_t_country_define
before insert on t_country_define
referencing old as old new as new for each row
begin
select s_country_id.nextval into :new.country_id from dual;
end;
/
第二种方法：
代码:
CREATE OR REPLACE TRIGGER TR1
BEFORE INSERT ON temp_table
FOR EACH ROW
declare
com_num NUMBER;
BEGIN
SELECT MAX(ID) INTO COM_NUM FROM TEMP_TABLE;
:NEW.ID:=COM_NUM+1;
END TR1;
/
5。限制用户登录：创建一个概要文件
create profile CLERK_PROFILE limit
session_per_user 1 #用户可拥有的会话次数
idle_time 10 #进程处于空闲状态的时间(10分钟)
然后就可以将该概要文件授予一个用户
alter user A profile CLERK_PROFILE;
6。使触发器为无效alter trigger yourtriggername disable
假如是对于某一个表的所有的触发器：
alter table yourtablename disable all triggers

更改数据库时间显示格式：
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。
1. 选取 TOP N 行记录
代码

上一篇：More Effective C++：不同new和delete

下一篇：WinHelp API命令方法