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命令方法