精妙的SQL语句.docx
《精妙的SQL语句.docx》由会员分享，可在线阅读，更多相关《精妙的SQL语句.docx（23页珍藏版）》请在冰豆网上搜索。
精妙的SQL语句
精妙的SQL语句
说明：
复制表（只复制结构,源表名：
a新表名：
b）
SQL:
select*intobfromawhere1<>1
说明：
拷贝表（拷贝数据,源表名：
a目标表名：
b）
SQL:
insertintob（a,b,c）selectd,e,ffromb;
[separator]
说明：
显示文章、提交人和最后回复时间
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）
--------------------------------------------------------------------------------
1.说明：
复制表（只复制结构,源表名：
a新表名：
b）
SQL:
select*intobfromawhere1<>1
2.说明：
拷贝表（拷贝数据,源表名：
a目标表名：
b）
SQL:
insertintob（a,b,c）selectd,e,ffroma;
3.说明：
显示文章、提交人和最后回复时间
SQL:
selecta.title,a.username,b.adddatefromtablea,（selectmax（adddate）adddatefromtablewheretable.title=a.title）b
4.说明：
外连接查询（表名1：
a表名2：
b）
SQL:
selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTERJOINbONa.a=b.c
5.说明：
日程安排提前五分钟提醒
SQL:
select*from日程安排wheredatediff（'minute',f开始时间,getdate（））>5
6.说明：
两张关联表，删除主表中已经在副表中没有的信息
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性别,生源地,高考总成绩
7.说明：
从数据库中去一年的各单位电话费统计（电话费定额贺电化肥清单两个表来源）
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'）
8.说明：
四表联查问题：
SQL:
select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....
9.说明：
得到表中最小的未使用的ID号
SQL:
SELECT（CASEWHENEXISTS（SELECT*FROMHandlebWHEREb.HandleID=1）THENMIN（HandleID）+1ELSE1END）asHandleID
FROMHandle
WHERENOTHandleIDIN（SELECTa.HandleID-1FROMHandlea）
9.SQL语句技巧
9.1、一个SQL语句的问题:
行列转换
select*fromv_temp
上面的视图结果如下:
user_namerole_name
-------------------------
系统管理员管理员
feng管理员
feng一般用户
test一般用户
想把结果变成这样:
user_namerole_name
---------------------------
系统管理员管理员
feng管理员,一般用户
test一般用户
===================
createtablea_test（namevarchar（20）,role2varchar（20））
insertintoa_testvalues（'李','管理員'）
insertintoa_testvalues（'張','管理員'）
insertintoa_testvalues（'張','一般用戶'）
insertintoa_testvalues（'常','一般用戶'）
createfunctionjoin_str（@contentvarchar（100））
returnsvarchar（2000）
as
begin
declare@strvarchar（2000）
set@str=''
select@str=@str+','+rtrim（role2）froma_testwhere[name]=@content
select@str=right（@str,len（@str）-1）
return@str
end
go
--调用：
select[name],dbo.join_str（[name]）role2froma_testgroupby[name]
--selectdistinctname,dbo.uf_test（name）froma_test
9.2、求助！
快速比较结构相同的两表
结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？
============================
给你一个测试方法，从northwind中的orders表取数据。
select*inton1fromorders
select*inton2fromorders
select*fromn1
select*fromn2
--添加主键，然后修改n1中若干字段的若干条
altertablen1addconstraintpk_n1_idprimarykey（OrderID）
altertablen2addconstraintpk_n2_idprimarykey（OrderID）
selectOrderIDfrom（select*fromn1
union
select*fromn2）agroupbyOrderIDhavingcount（*）>1
应该可以，而且将不同的记录的ID显示出来。
下面的适用于双方记录一样的情况，
select*fromn1whereorderidin
（
selectOrderIDfrom（select*fromn1
union
select*fromn2）agroupbyOrderIDhavingcount（*）>1
）
至于双方互不存在的记录是比较好处理的
--删除n1,n2中若干条记录
deletefromn1whereorderIDin（'10728','10730'）
deletefromn2whereorderIDin（'11000','11001'）
--*************************************************************
--双方都有该记录却不完全相同
select*fromn1whereorderidin
（
selectOrderIDfrom（select*fromn1
union
select*fromn2）agroupbyOrderIDhavingcount（*）>1
）
union
--n2中存在但在n1中不存的在10728,10730
select*fromn1whereOrderIDnotin（selectOrderIDfromn2）
union
--n1中存在但在n2中不存的在11000,11001
select*fromn2whereOrderIDnotin（selectOrderIDfromn1）
9.3、四种方法取表里n到m条纪录：
1.
selecttopm*into临时表（或表变量）fromtablenameorderbycolumnname--将topm笔插入
setrowcountn
select*from表变量orderbycolumnnamedesc
2.
selecttopn*from
（selecttopm*fromtablenameorderbycolumnname）a
orderbycolumnnamedesc
3.如果tablename里没有其他identity列，那么：
selectidentity（int）id0,*into#tempfromtablename
取n到m条的语句为：
select*from#tempwhereid0>=nandid0<=m
如果你在执行selectidentity（int）id0,*into#tempfromtablename这条语句的时候报错,那是因为你的DB中间的selectinto/bulkcopy属性没有打开要先执行：
execsp_dboption你的DB名字,'selectinto/bulkcopy',true
4.如果表里有identity属性，那么简单：
select*fromtablenamewhereidentitycolbetweennandm
5.如何删除一个表中重复的记录？
createtablea_dist（idint,namevarchar（20））
insertintoa_distvalues（1,'abc'）
insertintoa_distvalues（1,'abc'）
insertintoa_distvalues（1,'abc'）
insertintoa_distvalues（1,'abc'）
execup_distinct'a_dist','id'
select*froma_dist
createprocedureup_distinct（@t_namevarchar（30）,@f_keyvarchar（30））
--f_key表示是分組字段﹐即主鍵字段
as
begin
declare@maxinteger,@idvarchar（30）,@sqlvarchar（7999）,@typeinteger
select@sql='declarecur_rowscursorforselect'+@f_key+',count（*）from'+@t_name+'groupby'+@f_key+'havingcount（*）>1'
exec（@sql）
opencur_rows
fetchcur_rowsinto@id,@max
while@@fetch_status=0
begin
select@max=@max-1
setrowcount@max
select@type=xtypefromsyscolumnswhereid=object_id（@t_name）andname=@f_key
if@type=56
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@id
if@type=167
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''
exec（@sql）
fetchcur_rowsinto@id,@max
end
closecur_rows
deallocatecur_rows
setrowcount0
end
select*fromsystypes
select*fromsyscolumnswhereid=object_id（'a_dist'）
9.4.查询数据的最大排序问题（只能用一条语句写）
CREATETABLEhard（quchar（11）,cochar（11）,jenumeric（3,0））
insertintohardvalues（'A','1',3）
insertintohardvalues（'A','2',4）
insertintohardvalues（'A','4',2）
insertint
升级会员 