【网学网提醒】：网学会员编辑为广大网友搜集整理了：常用SQL操作技巧绩等信息，祝愿广大网友取得需要的信息，参考学习。
说明：复制表(只复制结构,源表名：a新表名：b)select*intobfromawhere1<>1
说明：拷贝表(拷贝数据,源表名：a目标表名：b)insertintob(a,b,c)selectd,e,ffromb;
说明：显示文章、提交人和最后回复时间selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b
说明：外连接查询(表名1：a表名2：b)selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
说明：日程安排提前五分钟提醒select*from日程安排wheredatediff('minute',f开始时间,getdate())>5
说明：两张关联表，删除主表中已经在副表中没有的信息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
说明：-select*fromstudentinfowherenotexists(select*fromstudentwherestudentinfo.i
d=student.id)and系名称='"&;strdepartmentname&;"'and专业名称='"&;strprofessionname&;"'orderby性别,生源地,高考总成绩
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
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')
说明：四表联查问题select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....
说明：得到表中最小的未使用
的ID号
Select(CASEWHENEXISTS(Select*FROMHandlebWhereb.HandleID=1)THENMIN(HandleID)+1ELSE1END)asHandleIDFROMHandleWhereNOTHandleIDIN(Selecta.HandleID-1FROMHandlea)
一个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)asbegindeclare@strvarchar(2000)set@str=''select@str=@str+','+rtrim(role2)froma_testwhere[name]=@contentselect@str=right(@str,len(@str)-1)return@str
endgo
--调用：select[name],dbo.join_str([name])role2froma_testgroupby[name]
--selectdistinctname,dbo.uf_test(name)froma_test
快速比较结构相同的两表结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？============================给你一个测试方法，从northwind中的orders表取数据。select*inton1fromordersselect*inton2fromorders
select*fromn1select*fromn2
--添加主键，然后修改n1中若干字段的若干条altertablen1addconstraintpk_n1_idprimarykey(OrderID)altertablen2addconstraintpk_n2_idprimarykey(OrderID)
selectorderIDfrom(select*fromn1unionselect*fromn2)agroupbyorderIDhavingcount(*)>1
应该可以，而且将不同的记录的ID显示出来。下面的适用于双方记录一样的情况，
select*fromn1whereorderidin(selectorderIDfrom(select*fromn1unionselect*fromn2)agroupbyorderIDhavingcount(*)>1)至于双方互不存在的记录是比较好处理的--删除n1,n2中若干条记录deletefromn1whereorderIDin('10728','10730')deletefromn2whereorderIDin('11000','11001')
--*************************************************************--双方都有该记录却不完全相同select*fromn1whereorderidin(selectorderIDfrom(select*fromn1unionselect*fromn2)agroupbyorderIDhavingcount(*)>1)union--n2中存在但在n1中不存的在10728,10730select*fromn1whereorderIDnotin(selectorderIDfromn2)union--n1中存在但在n2中不存的在11000,11001select*fromn2whereorderIDnotin(selectorderIDfromn1)
四种方法取表里n到m条纪录：
1.selecttopm*into临时表(或表变量)fromtablenameorderbycolumnname--将topm笔插入
setrowcountnselect*from表变量orderbycolumnnamedesc
2.
selecttopn*from(selecttopm*fromtablenameorderbycolumnname)aorderbycolumnnamedesc
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
如何删除一个表中重复的记录？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表示是分组字段﹐即主键字段asbegindeclare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeintegerselect@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'exec(@sql)opencur_rowsfetchcur_rowsinto@id,@maxwhile@@fetch_status=0beginselect@max=@max-1setrowcount@maxselect@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_keyif@type=56select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@idif@type=167
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''exec(@sql)fetchcur_rowsinto@id,@maxendclosecur_rowsdeallocatecur_rowssetrowcount0end
select*fromsystypesselect*fromsyscolumnswhereid=object_id('a_dist')
查询数据的最大排序问题（只能用一条语句写）CreateTABLEhard(quchar(11),cochar(11),jenumeric(3,0))
insertintohardvalues('A','1',3)insertintohardvalues('A','2',4)insertintohardvalues('A','4',2)insertintohardvalues('A','6',9)insertintohardvalues('B','1',4)insertintohardvalues('B','2',5)insertintohardvalues('B','3',6)insertintohardvalues('C','3',4)insertintohardvalues('C','6',7)insertintohardvalues('C','2',3)
要求查询出来的结果如下：
qucoje--------------------------A69A24B36B25C67C34
就是要按qu分组，每组中取je最大的前2位！！而且只能用一句sql语句！！！select*fromhardawherejein(selecttop2jefromhardbwherea.qu=b.quorderbyje)
求删除重复记录的sql语句？怎样把具有相同字段的纪录删除，只留下一条。例如，表test里有id,name字段如果有name相同的记录只留下一条，其余的删除。name的内容不定，相同的记录数不定。有没有这样的sql语句？
==============================A:一个完整的解决方案：
将
重复的记录记入temp1表:select[标志字段id],count(*)intotemp1from[表名]groupby[标志字段id]havingcount(*)>1
2、将不重复的记录记入temp1表:inserttemp1select[标志字段id],count(*)from[表名]groupby[标志字段id]havingcount(*)=1
3、作一个包含所有不重复记录的表：select*intotemp2from[表名]where标志字段idin(select标志字段idfromtemp1)
4、删除重复表:delete[表名]
5、恢复表：insert[表名]select*fromtemp2
6、删除临时表:droptabletemp1droptabletemp2================================B: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表示是分组字段﹐即主键字段asbegindeclare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeintegerselect@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'exec(@sql)opencur_rowsfetchcur_rowsinto@id,@maxwhile@@fetch_status=0beginselect@max=@max-1setrowcount@maxselect@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_keyif@type=56
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@idif@type=167select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''exec(@sql)fetchcur_rowsinto@id,@maxendclosecur_rowsdeallocatecur_rowssetrowcount0end
select*fromsystypesselect*fromsyscolumnswhereid=object_id('a_dist')
行列转换--普通
假设有张学生成绩表(CJ)如下NameSubjectResult张三语文80张三数学90张三物理85李四语文85李四数学92李四物理82
想变成语文数学物理张三809085李四859282
declare@sqlvarchar(4000)set@sql='selectName'select@sql=@sql+',sum(caseSubjectwhen'''+Subject+'''thenResultend)['+Subject+']'from(selectdistinctSubjectfromCJ)asaselect@sql=@sql+'fromtestgroupbyname'exec(@sql)
行列转换--合并
有表A,idpid111213212231如何化成表B:idpid11,2,3
21,231
创建一个合并的函数createfunctionfmerg(@idint)returnsvarchar(8000)asbegindeclare@strvarchar(8000)set@str=''select@str=@str+','+cast(pidasvarchar)from表Awhereid=@idset@str=right(@str,len(@str)-1)return(@str)Endgo
--调用自定义函数得到结果selectdistinctid,dbo.fmerg(id)from表A
如何取得一个数据表的所有列名
方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。SQL语句如下：declare@objidint,@objnamechar(40)set@objname='tablename'
select@objid=idfromsysobjectswhere
id=object_id(@objname)select'Column_name'=namefromsyscolumnswhereid=@objidorderbycolid
或
Select*FROMINFORMATION_SCHEMA
说明:得到表中最小的未使用的ID号
SQL:SELECT(CASEWHENEXISTS(SELECT*FROMHandlebWHEREb.HandleID=1)THENMIN(HandleID)+1ELSE1END)asHandleIDFROMHandleWHERENOTHandleIDIN(SELECTa.HandleID-1FROMHandlea)
在数据库中如何分类、分组并总计SQL数据总计聚合您可以通过显示每个组的小计进一步总计数据。SQL的ROLLUP运算符为每个组显示一个额外的记录，一个小计。那个记录是用聚合函数在每个组中求所有记录的值的结果。下面的语句为每个组合计OrderTotal列。
SELECTCustomer,OrderNumber,Sum(Cost*Quantity)ASOrderTotal
FROMOrders
GROUPBYCustomer,OrderNumber
WITHROLLUP
一个包含20和25这两个OrderTotal值的组的ROLLUP行将显示OrderTotal值45。ROLLUP结果的第一个值是唯一的，因为它求所有组记录的值。那个值是整个记录集的总和。
ROLLUP不支持聚合函数中的DISTINCT或GROUPBYALL子句。
总计每一列
CUBE运算符比ROLLUP更进一步，它返回每个组中每个值的总数。得到的结果与ROLLUP相似，但CUBE包括组中每一列的一个额外记录。下面的语句显示每个组的小计和每名顾客的一个额外总数。
SELECTCustomer,OrderNumber,Sum(Cost*Quantity)ASOrderTotal
FROMOrders
GROUPBYCustomer,OrderNumber
WITHCUBE
用CUBE得到的总计最为复杂。不仅完成聚合与ROLLUP的工作，而且还求定义组的其它列的值。也就是说，CUBE总计每一个可能的列组合。
CUBE不支持GROUPBYALL。
给总计排序
当CUBE的结果杂乱无章时（一般都是这样），可以增加一个GROUPING函数，如下所示：
SELECTGROUPING(Customer),OrderNumber,Sum(Cost*Quantity)ASOrderTotal
FROMOrders
GROUPBYCustomer,OrderNumber
WITHCUBE
其结果包括每一行的两个额外的值。
??
值1表明左边的值是一个总计值——ROLLUP或CUBE的运算符的结果。值0表明左边的值是一个原始GROUPBY子句产生的详细记录。
简单谈基于SQLSERVER分页存储过程的演进
针对数据库数据在UI界面上的分页是老生常谈的问题了，网上很容易找到各种―通用存储过程‖代码，而且有些还定制查询条件，看上去使用很方便。笔者打算通过本文也来简单谈一下基于SQLSERVER2000的分页存储过程，同时谈谈SQLSERVER2005下分页存储过程的演进。在进行基于UI显示的数据分页时，常见的数据提取方式主要有两种。第一种是从数据库提取所有数据然后在系统应用程序层进行数据分页，显示当前页数据。第二种分页方式为从数据库取出需要显示的一页数据显示在UI界面上。以下是笔者对两种实现方
式所做的优缺点比较，针对应用程序编写，笔者以.NET技术平台为例。
类别第一种第二种
SQL语句语句简单，兼容性好看具体情况
代码编写很少较多
设计时完全支持部分支持
性能数据越大性能越差良好，跟SQL语句有关
对于第一种情况本文不打算举例，第二种实现方式笔者只以两次TOP方式来进行讨论。在编写具体SQL语句之前，定义以下数据表。数据表名称为：Production.Product。Production为SQLSERVER2005中改进后的数据表架构，对举例不造成影响。包含的字段为：列名ProductIDName数据类型IntNvarchar(50)允许空说明产品ID，PK。产品名称。
不难发现以上表结构来自SQLSERVER2005样例数据库AdventureWorks的Production.Product表，并且只取其中两个字段。分页相关元素：PageIndex–页面索引计数，计数0为第一页。PageSize–每个页面显示大小。RecordCount–总记录数。PageCount–页数。对于后两个参数，笔者在存储过程中以输出参数提供。1．SQLSERVER2000中的TOP分页CREATEPROCEDURE[Zhzuo_GetItemsPage]@PageIndexINT,/*@PageIndex从计数,0为第一页*/@PageSizeINT,/*页面大小*/@RecordCountINTOUT,/*总记录数*/@PageCountINTOUT/*页数*/AS/*获取记录数*/SELECT@RecordCount=COUNT(*)FROMProduction.Product/*计算页面数据*/SET@PageCount=CEILING(@RecordCount*1.0/@PageSize)/*TOP记录数*/DECLARE@TOPCOUNTINTSET@TOPCOUNT=@RecordCount-@PageSize*@PageIndexDECLARE@SQLSTRNVARCHAR(1000)IF@PageIndex=0OR@PageCount<=1BEGINSET@SQLSTR=N'SELECTTOP'+STR(@PageSize)+
'ProductID,NameFROMProduction.ProductORDERBYProductIDDESC'ENDELSEBEGINIF@PageIndex=@PageCount-1BEGINSET@SQLSTR=N'SELECT*FROM(SELECTTOP'+STR(@TOPCOUNT)+'ProductID,NameFROMProduction.ProductORDERBYProductIDASC)TORDERBYProductIDDESC'ENDELSEBEGINSET@SQLSTR=N'SELECTTOP'+STR(@PageSize)+'*FROM(SELECTTOP'+STR(@TOPCOUNT)+'ProductID,NameFROMProduction.ProductORDERBYProductIDASC)TORDERBYProductIDDESC'ENDEND/*执行*/EXEC(@SQLSTR)
以上存储过程对页数进行判断，如果是第一页或最后一页，进行特殊处理。其他情况使用2次TOP翻转。其中排序条件为ProductID倒序。最后通过EXECUTE执行SQL字符串拼串。2．SQLSERVER2005中的TOP分页CREATEPROCEDURE[dbo].[Zhzuo_GetItemsPage2005TOP]
@PageIndexINT,@PageSizeINT,@RecordCountINTOUT,@PageCountINTOUTAS/*获取记录数*/SELECT@RecordCount=COUNT(*)FROMProduction.Product/*计算页面数据*/SET@PageCount=CEILING(@RecordCount*1.0/@PageSize)/*TOP记录数*/DECLARE@TOPCOUNTINTSET@TOPCOUNT=@RecordCount-@PageSize*@PageIndex/*基于SQLSERVER2005*/IF@PageIndex=0OR@PageCount<=1BEGINSELECTTOP(@PageSize)ProductID,NameFROMP
roduction.ProductORDERBYProductIDDESCENDELSEBEGINIF@PageIndex=@PageCount-1BEGINSELECT*FROM(SELECTTOP(@TOPCOUNT)ProductID,NameFROMProduction.ProductORDERBYProductIDASC)TORDERBYProductIDDESCEND
ELSEBEGINSELECTTOP(@PageSize)*FROM(SELECTTOP(@TOPCOUNT)ProductID,NameFROMProduction.ProductORDERBYProductIDASC)TORDERBYProductIDDESCENDEND以上存储过程是使用2005的TOP(表达式)新功能，避免了字符串拼串，使结构化查询语言变得简洁。实现的为同样的功能。3．SQLSERVER2005中的新分页CREATEPROCEDURE[dbo].[Zhzuo_GetItemsPage2005]@PageIndexINT,@PageSizeINT,@RecordCountINTOUT,@PageCountINTOUTAS/*获取记录数*/SELECT@RecordCount=COUNT(*)FROMProduction.Product/*计算页面数据*/SET@PageCount=CEILING(@RecordCount*1.0/@PageSize)/*基于SQLSERVER2005*/SELECTSerialNumber,ProductID,NameFROM(SELECTProductID,Name,ROW_NUMBER()OVER(ORDERBYProductIDDESC)ASSerialNumberFROMProduction.Product)ASTWHERET.SerialNumber>(@PageIndex*@PageSize)andT.SerialNumber<=((@PageI
ndex+1)*@PageSize)
第三个存储过程使用2005下新的功能，实现的分页存储过程功能更加简单明了，而且更加容易理解。注意这里的ProductID为主键,根据ProductID进行排序生成ROW_NUMBER，通过ROW_NUMBER来确定具体的页数。通过对三个分页存储过程的比较，可见SQLSERVER的TSQL语言对分页功能的支持进步不少。使分页实现趋向于简单化。
如何在存储过程中得到被调用存储过程的结果集
假设有表：CREAETTABLEa(idint,namevarchar(50))INSERTINTOaSELECT1,'AAAA'INSERTINTOaSELECT2,'BBBB'存储过程：CREAETEPROCusp_fetch_aASSELECT*FROMa
方法一：CREATETABLE#tmp(idint,namevarchar(50))INSERTINTO#tmpEXECusp_fetch_aDROPTABLE#tmp【此处不能用表变量】方法二：SELECTa.*FROMOPENROWSET('SQLOLEDB','ServerName';'Login';'Passwd','execDatabase.dbo.usp_fetch_a')ASa
