如何從一位菜鳥蛻變成為高手靈活使用的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) ¦¦ /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
