substr(string,1,8)

1.
//將一個表中符合條件的記錄批量更新到另外一張表。（假設表名為：toffice，tofficetemp）
??????update toffice a
??????set a.office_code = (select b.office_code from tofficetemp b where a.jgdm = b.jgdm)
??????where a.jgdm in (select jgdm from tofficetemp)
??????update tofficenexas set isuse = '0' where rowid in?
??????(select rowid from tofficenexas a where?
??????rowid !=(select max(rowid) from tofficenexas b where a.office_id = b.office_id and
??????a.father_office_id = b.father_office_id and a.big_kind_id =b.big_kind_id))

2

//查詢某個時間段的值

select * from tdespatch t
where t.out_time between to_date('2005-10-18 15:47:42','yyyy-mm-dd hh24:mi:ss')
?and to_date('2005-10-20 10:47:42','yyyy-mm-dd hh24:mi:ss')


3。
//復制表(只復制結構,源表名：a?新表名：b)
SQL:?select?*?into?b?from?a?where?1<>1

4。
//拷貝表(拷貝數據,源表名：a?目標表名：b)
SQL:?insert?into?b(a,?b,?c)?select?d,e,f?from?b;

5。
說明：顯示文章、提交人和最后回復時間
SQL:?select?a.title,a.username,b.adddate?from?table?a,(select?max(adddate)?adddate?from?table?where?table.title=a.title)?b

說明：外連接查詢(表名1：a?表名2：b)
SQL:?select?a.a,?a.b,?a.c,?b.c,?b.d,?b.f?from?a?LEFT?OUT?JOIN?b?ON?a.a?=?b.c

說明：日程安排提前五分鐘提醒
SQL:??select?*?from?日程安排?where?datediff('minute',f開始時間,getdate())>5


說明：兩張關聯表，刪除主表中已經在副表中沒有的信息
SQL:??
delete?from?info?where?not?exists?(?select?*?from?infobz?where?info.infid=infobz.infid?)?

說明：--
SQL:??
SELECT?A.NUM,?A.NAME,?B.UPD_DATE,?B.PREV_UPD_DATE
??FROM?TABLE1,?
????(SELECT?X.NUM,?X.UPD_DATE,?Y.UPD_DATE?PREV_UPD_DATE
????????FROM?(SELECT?NUM,?UPD_DATE,?INBOUND_QTY,?STOCK_ONHAND
????????????????FROM?TABLE2
??????????????WHERE?TO_CHAR(UPD_DATE,'YYYY/MM')?=?TO_CHAR(SYSDATE,?'YYYY/MM'))?X,?
????????????(SELECT?NUM,?UPD_DATE,?STOCK_ONHAND
????????????????FROM?TABLE2
??????????????WHERE?TO_CHAR(UPD_DATE,'YYYY/MM')?=?
????????????????????TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,?'YYYY/MM')?&brvbar;&brvbar;?'/01','YYYY/MM/DD')?-?1,?'YYYY/MM')?)?Y,?
????????WHERE?X.NUM?=?Y.NUM?（+）
??????????AND?X.INBOUND_QTY?+?NVL(Y.STOCK_ONHAND,0)?<>?X.STOCK_ONHAND?)?B
WHERE?A.NUM?=?B.NUM

說明：--
SQL:??
select?*?from?studentinfo?where?not?exists(select?*?from?student?where?studentinfo.id=student.id)?and?系名稱='"&strdepartmentname&"'?and?專業名稱='"&strprofessionname&"'?order?by?性別,生源地,高考總成績

說明：
從數據庫中去一年的各單位電話費統計(電話費定額賀電化肥清單兩個表來源）
SQL:?
SELECT?a.userper,?a.tel,?a.standfee,?TO_CHAR(a.telfeedate,?'yyyy')?AS?telyear,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'01',?a.factration))?AS?JAN,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'02',?a.factration))?AS?FRI,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'03',?a.factration))?AS?MAR,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'04',?a.factration))?AS?APR,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'05',?a.factration))?AS?MAY,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'06',?a.factration))?AS?JUE,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'07',?a.factration))?AS?JUL,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'08',?a.factration))?AS?AGU,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'09',?a.factration))?AS?SEP,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'10',?a.factration))?AS?OCT,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'11',?a.factration))?AS?NOV,
??????SUM(decode(TO_CHAR(a.telfeedate,?'mm'),?'12',?a.factration))?AS?DEC
FROM?(SELECT?a.userper,?a.tel,?a.standfee,?b.telfeedate,?b.factration
????????FROM?TELFEESTAND?a,?TELFEE?b
????????WHERE?a.tel?=?b.telfax)?a
GROUP?BY?a.userper,?a.tel,?a.standfee,?TO_CHAR(a.telfeedate,?'yyyy')

說明：四表聯查問題：
SQL:?select?*?from?a?left?inner?join?b?on?a.a=b.b?right?inner?join?c?on?a.a=c.c??inner?join?d?on?a.a=d.d?where?.....

說明：得到表中最小的未使用的ID號
SQL:
SELECT?(CASE?WHEN?EXISTS(SELECT?*?FROM?Handle?b?WHERE?b.HandleID?=?1)?THEN?MIN(HandleID)?+?1?ELSE?1?END)?as?HandleID
?FROM??Handle
?WHERE?NOT?HandleID?IN?(SELECT?a.HandleID?-?1?FROM?Handle?a)
6?? 根據父表 只取子表最新的一條記錄{

SQL> select * from testa ;

?

??????? F1

----------

???????? 1

???????? 2

???????? 3

SQL> select * from testb ;

??????? F1 F2

---------- -------------------

???????? 1 2006-04-10 14:56:41

???????? 1 2006-04-10 14:56:53

???????? 1 2006-04-10 14:57:00

???????? 2 2006-04-10 14:57:08

???????? 3 2006-04-10 14:57:19

???????? 3 2006-04-10 14:57:25?

6 rows selected.

SQL> select testa.f1,v1.f1,v1.f2 from testa ,(select f1,f2,row_number() over (partition by f1 order

by f2 desc) rn from testb) v1 where testa.f1 = v1.f1 and v1.rn = 1 ;

??????? F1???????? F1 F2

---------- ---------- -------------------

???????? 1??????? ??1 2006-04-10 14:57:00

???????? 2????????? 2 2006-04-10 14:57:08

???????? 3????????? 3 2006-04-10 14:57:25
------------------------------------------

7 查詢語句的優化
select t.* from acc$ t
where
not exists （在此不使用not in 是為了性能上的考慮）
(select 'a' from crm$ a
where
a.客戶名=t.用戶名稱
--a.客戶號=t.用戶編號

)