精妙SQL语句

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
说明:复制表(只复制结构,源表名:a新表名:b)

SQL:select*intobfromawhere1<>1

说明:拷贝表(拷贝数据,源表名:a目标表名:b)

SQL:insertintob(a,b,c)selectd,e,ffromb;

说明:显示文章、提交人和最后回复时间

SQL:selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b

说明:外连接查询(表名1:a表名2:b)

SQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c

说明:日程安排提前五分钟提醒

SQL:select*from日程安排wheredatediff('minute',f开始时间,getdate())>5

说明:两张关联表,删除主表中已经在副表中没有的信息

SQL:

deletefrominfowherenotexists(select*frominfobzwhereinfo.infid=infobz.infid)

说明:--

SQL:

SELECTA.NUM,A.NAME,B.UPD_DATE,B.PREV_UPD_DATE

FROMTABLE1,

(SELECTX.NUM,X.UPD_DATE,Y.UPD_DATEPREV_UPD_DATE

FROM(SELECTNUM,UPD_DATE,INBOUND_QTY,STOCK_ONHAND

FROMTABLE2

WHERETO_CHAR(UPD_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM'))X,

(SELECTNUM,UPD_DATE,STOCK_ONHAND

FROMTABLE2

WHERETO_CHAR(UPD_DATE,'YYYY/MM')=

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM')¦¦'/01','YYYY/MM/DD')-1,'YYYY/MM'))Y,

WHEREX.NUM=Y.NUM(+)

ANDX.INBOUND_QTY+NVL(Y.STOCK_ONHAND,0)<>X.STOCK_ONHAND)B

WHEREA.NUM=B.NUM

说明:--

SQL:

select*fromstudentinfowherenotexists(select*fromstudentwherestudentinfo.id=student.id)and系名称='"&strdepartmentname&"'and专业名称='"&strprofessionname&"'orderby性别,生源地,高考总成绩

说明:

从中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源)

SQL:

SELECTa.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,'yyyy')AStelyear,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'01',a.factration))ASJAN,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'02',a.factration))ASFRI,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'03',a.factration))ASMAR,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'04',a.factration))ASAPR,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'05',a.factration))ASMAY,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'06',a.factration))ASJUE,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'07',a.factration))ASJUL,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'08',a.factration))ASAGU,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'09',a.factration))ASSEP,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'10',a.factration))ASOCT,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'11',a.factration))ASNOV,

SUM(decode(TO_CHAR(a.telfeedate,'mm'),'12',a.factration))ASDEC

FROM(SELECTa.userper,a.tel,a.standfee,b.telfeedate,b.factration

FROMTELFEESTANDa,TELFEEb

WHEREa.tel=b.telfax)a

GROUPBYa.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,'yyyy')

说明:四表联查问题:

SQL:select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....

说明:得到表中最小的未使用的ID号

SQL:

SELECT(CASEWHENEXISTS(SELECT*FROMHandlebWHEREb.HandleID=1)THENMIN(HandleID)+1ELSE1END)asHandleID

FROMHandle

WHERENOTHandleIDIN(SELECTa.HandleID-1FROMHandlea)共2页  1 
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
文章标签: sql date 电话 c
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭