如何从一位菜鸟蜕变成为高手灵活使用的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)
