熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

精妙SQL語句介紹

2013-11-13 15:38:40  來源: Oracle 

  如何從一位菜鳥蛻變成為高手靈活使用的SQL語句是必不可少的本文收集了部分比較經典常用的SQL語句供大家參考希望對大家有所幫助

  說明復制表(只復制結構源表名a 新表名b)  

  SQL: select * into b from a where <>

  說明拷貝表(拷貝數據源表名a 目標表名b)  

  SQL: insert into b(a b c) select def from b;

  說明顯示文章提交人和最後回復時間  

  SQL: select atitleausernamebadddate from table a(select max(adddate) adddate from table where tabletitle=atitle) b

  說明外連接查詢(表名a 表名b)  

  SQL: select aa ab ac bc bd bf from a LEFT OUT JOIN b ON aa = bc

  說明日程安排提前五分鐘提醒  

  SQL: select * from 日程安排 where datediff(minutef開始時間getdate())>  

  說明兩張關聯表刪除主表中已經在副表中沒有的信息

  SQL:   

  delete from info where not exists ( select * from infobz where infoinfid=infobzinfid

  說明

  SQL:   

  SELECT ANUM ANAME BUPD_DATE BPREV_UPD_DATE

  FROM TABLE

  (SELECT XNUM XUPD_DATE YUPD_DATE PREV_UPD_DATE

  FROM (SELECT NUM UPD_DATE INBOUND_QTY STOCK_ONHAND

  FROM TABLE

  WHERE TO_CHAR(UPD_DATEYYYY/MM) = TO_CHAR(SYSDATE YYYY/MM)) X

  (SELECT NUM UPD_DATE STOCK_ONHAND

  FROM TABLE

  WHERE TO_CHAR(UPD_DATEYYYY/MM) =

  TO_CHAR(TO_DATE(TO_CHAR(SYSDATE YYYY/MM) &brvbar;&brvbar; /YYYY/MM/DD) YYYY/MM) Y

  WHERE XNUM = YNUM (+)

  AND XINBOUND_QTY + NVL(YSTOCK_ONHAND) <> XSTOCK_ONHAND B

  WHERE ANUM = BNUM

  說明

  SQL:   

  select * from studentinfo where not exists(select * from student where studentinfoid=studentid) and 系名稱=&strdepartmentname& and 專業名稱=&strprofessionname& order by 性別生源地高考總成績

  說明

  從數據庫中去一年的各單位電話費統計(電話費定額賀電化肥清單兩個表來源)

  SQL:  

  SELECT auserper atel astandfee TO_CHAR(atelfeedate yyyy) AS telyear

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS JAN

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS FRI

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS MAR

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS APR

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS MAY

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS JUE

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS JUL

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS AGU

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS SEP

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS OCT

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS NOV

  SUM(decode(TO_CHAR(atelfeedate mm) afactration)) AS DEC

  FROM (SELECT auserper atel astandfee btelfeedate bfactration

  FROM TELFEESTAND a TELFEE b

  WHERE atel = btelfax) a

  GROUP BY auserper atel astandfee TO_CHAR(atelfeedate yyyy)

  說明四表聯查問題  

  SQL: select * from a left inner join b on aa=bb right inner join c on aa=cc inner join d on aa=dd where

  說明得到表中最小的未使用的ID號

  SQL: 

  SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE bHandleID = ) THEN MIN(HandleID) + ELSE END) as HandleID

  FROM Handle

  WHERE NOT HandleID IN (SELECT aHandleID FROM Handle a) 


From:http://tw.wingwit.com/Article/program/Oracle/201311/17064.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.