SQL一些常用技巧总结.docx
《SQL一些常用技巧总结.docx》由会员分享，可在线阅读，更多相关《SQL一些常用技巧总结.docx（49页珍藏版）》请在冰豆网上搜索。
SQL一些常用技巧总结
/*-------------------------------------------------------------------
*表格旋转/转置:
*说明
不支持下列数据类型:
image,text,ntext,hierarchyid,geometry,geography.
2000好像没有fn_varbintohexstr,所以不支持varbinary,binary,timestamp,
要扩展自己写bin2str函数.
2008的varbinary/binary可以直接convert,详细参考联机/MSDN,但懒得判断
版本了,一律用fn_varbintohexstr.
2000内层受长度8000的限制,某列数据超过8000长度肯定报错.
2005/2008检测用varchar（max）
轴向列转sysname,超过128截断.其它列除有限的几个要显式转换的数据类型,
一律用rtrim（）隐式转换,具体看代码.
有处理NULL值,不至于被一个NULL玩死.
-------------------------------------------------------------------*/
CREATEPROCEDUREp_rotate
（
@tablesysname,--表/视图
@axissysname=null,--轴,旋转后作为字段名,默认第列
@renamesysname=null,--重命名轴
@styleint=121--日期时间转换样式
）
AS
SETNOCOUNTON
ifobject_id（@table）isnullreturn--不废话
declare@innervarchar（8000）--定义内层exec变量
declare@firstvarchar（8000）--每行数据的第一列即原字段名变成第列
declare@rowsvarchar（8000）--读取每列数据作为行数据
declare@unionvarchar（8000）--每行数据unionall
declare@maxvarchar（10）
declare@typeint
select@axis=isnull（@axis,（selectnamefromsyscolumnswhereid=object_id（@table）andcolid=1））
select@type=xtypefromsyscolumnswhereid=object_id（@table）andname=@axis
if@typein（34,35,99,240）--image,text,ntext,hierarchyid,geometry,geography
or@@versionnotlike'%Server200[58]%'and@typein（165,173,189）--varbinary,binary,timestamp
begin
selectnamefromsystypeswherextype=@type
return
end
select@rename=isnull（@rename,@axis）,@max=casewhen@@versionlike'%Server200[58]%'then'max'else'8000'end
--构造内层exec
select
@inner=isnull（@inner+',',''）+'@'+ltrim（colid）+'varchar（'+@max+'）',
@first=isnull（@first+',',''）+'@'+ltrim（colid）+'=''select['+@rename+']='''''+name+'''''''',
@rows=isnull（@rows,''）+char（13）+char（10）+'select@'+ltrim（colid）+'=@'+ltrim（colid）+'+'',[''+isnull（'+
case
when@type=189then'master.sys.fn_varbintohexstr（convert（binary（8）,['+@axis+']））'--timestamp
when@typein（165,173）then'left（master.sys.fn_varbintohexstr（['+@axis+']）,128）'--varbinary,binary
when@typein（175,239）then'rtrim（convert（sysname,['+@axis+']））'--char,nchar
when@typein（40,41,42,43,58,61）then'convert（sysname,['+@axis+'],'+ltrim（@style）+'）'--date,time,datetime2,datetimeoffset,smalldatetime,datetime
else'convert（sysname,['+@axis+']）'
end+',''NULL''）+'']=''+isnull（quotename（'+
case
whenxtype=189then'master.sys.fn_varbintohexstr（convert（binary（8）,['+name+']））'--timestamp
whenxtypein（165,173）then'master.sys.fn_varbintohexstr（['+name+']）'--varbinary,binary
--whenxtypein（60,122）then'convert（varchar（50）,['+name+'],2）'--money,smallmoney--需要精细控制类型转换这里添加
whenxtypein（40,41,42,43,58,61）then'convert（varchar（50）,['+name+'],'+ltrim（@style）+'）'--date,time,datetime2,datetimeoffset,smalldatetime,datetime
whenxtypein（98,241）then'convert（varchar（'+@max+'）,['+name+']）'--sql_variant,xml
else'rtrim（['+name+']）'
end+',char（39））,''null''）from['+@table+']',
@union=isnull（@union+'+''unionall''+',''）+'@'+ltrim（colid）
fromsyscolumns
whereid=object_id（@table）andname<>@axisand（xtypenotin（34,35,99,165,173,189,240）or@@versionlike'%Server200[58]%'andxtypenotin（34,35,99,240））
orderbycolid
--print/exec
exec（'declare'+@inner+'
select'+@first+@rows+'
exec（'+@union+'）'）
SETNOCOUNTOFF
-->测试数据：
student
ifobject_id（'student'）isnotnulldroptablestudent
createtablestudent（姓名varchar（8）,学号int,专业varchar（8）,性别varchar（8）,高数int,化学int,英语int,物理int,总分float,个人平均分float,名次int）
insertintostudent
select'学生壬',1009,'热能','女',89,93,84,90,356.00,89.00,1unionall
select'学生甲',1001,'冶金','男',88,87,78,98,351.00,87.75,2unionall
select'学生癸',1010,'热能','女',83,91,85,89,348.00,87.00,3unionall
select'学生丙',1003,'冶金','女',97,90,70,89,346.00,86.50,4unionall
select'学生戊',1005,'冶金','男',91,99,69,81,340.00,85.00,5unionall
select'学生寅',1013,'机械','女',90,80,83,76,329.00,82.25,6unionall
select'学生卯',1014,'机械','男',81,92,88,62,323.00,80.75,7unionall
select'学生辛',1008,'热能','女',70,80,80,84,314.00,78.50,8unionall
select'学生辰',1015,'机械','男',83,91,74,65,313.00,78.25,9unionall
select'学生丁',1004,'冶金','女',79,69,83,78,309.00,77.25,10unionall
select'学生丑',1012,'机械','男',92,70,77,60,299.00,74.75,11unionall
select'学生庚',1007,'热能','男',76,86,59,74,295.00,73.75,12unionall
select'学生子',1011,'机械','男',69,84,71,71,295.00,73.75,13unionall
select'学生乙',1002,'冶金','男',85,79,72,57,293.00,73.25,14unionall
select'学生己',1006,'热能','男',85,73,66,69,293.00,73.25,15
select*fromstudent
/*
姓名学号专业性别高数化学英语物理总分个人平均分名次
----------------------------------------------------------------------------------------------------------------
学生壬1009热能女89938490356891
学生甲1001冶金男8887789835187.752
学生癸1010热能女83918589348873
学生丙1003冶金女9790708934686.54
学生戊1005冶金男91996981340855
学生寅1013机械女9080837632982.256
学生卯1014机械男8192886232380.757
学生辛1008热能女7080808431478.58
学生辰1015机械男8391746531378.259
学生丁1004冶金女7969837830977.2510
学生丑1012机械男9270776029974.7511
学生庚1007热能男7686597429573.7512
学生子1011机械男6984717129573.7513
学生乙1002冶金男8579725729373.2514
学生己1006热能男8573666929373.2515
*/
--转置
execp_rotate'student'
/*
姓名学生壬学生甲学生癸学生丙学生戊学生寅学生卯学生辛学生辰学生丁学生丑学生庚学生子学生乙学生己
----------------------------------------------------------------------------------------------------
学号100910011010100310051013101410081015100410121007101110021006
专业热能冶金热能冶金冶金机械机械热能机械冶金机械热能机械冶金热能
性别女男女女男女男女男女男男男男男
高数898883979190817083799276698585
化学938791909980928091697086847973
英语847885706983888074837759717266
物理909889898176628465786074715769
总分356351348346340329323314313309299295295293293
个人平均分8987.758786.58582.2580.7578.578.2577.2574.7573.7573.7573.2573.25
名次123456789101112131415
*/
得到前N天的日期表
selectdateadd（day,-number-1,getdate（））frommaster..spt_valueswheretype='P'andnumber<5orderbynumberdesc
查询指定节点及其所有子节点的函数（表格形式显示）
省市数据归纳
SQL2000下行专列
createprochang_lie
@table_namenvarchar（50）
as
declare@snvarchar（4000）
select@s=isnull（@s+'unionall',''）+'select[zone],[to_zone]='+quotename（Name,''''）--isnull（@s+'unionall',''）去掉字符串@s中第一个unionall
+',[basic]='+quotename（Name）+'from'+@table_name
fromsyscolumnswhereID=object_id（@table_name）andNamenotin（'zone'）--排除不转换的列
orderbyColid
exec（'select*from（'+@s+'）torderby[zone],[to_zone]'）
合并列值
--*******************************************************************************************
表结构，数据如下：
idvalue
-----------
1aa
1bb
2aaa
2bbb
2ccc
需要得到结果：
idvalues
-----------------
1aa,bb
2aaa,bbb,ccc
即：
groupbyid,求value的和（字符串相加）
1.旧的解决方法（在sqlserver2000中只能用函数解决。
）
--=============================================================================
createtabletb（idint,valuevarchar（10））
insertintotbvalues（1,'aa'）
insertintotbvalues（1,'bb'）
insertintotbvalues（2,'aaa'）
insertintotbvalues（2,'bbb'）
insertintotbvalues（2,'ccc'）
go
--1.创建处理函数
CREATEFUNCTIONdbo.f_strUnite（@idint）
RETURNSvarchar（8000）
AS
BEGIN
DECLARE@strvarchar（8000）
SET@str=''
SELECT@str=@str+','+valueFROMtbWHEREid=@id
RETURNSTUFF（@str,1,1,''）
END
GO
--调用函数
SELECtid,value=dbo.f_strUnite（id）FROMtbGROUPBYid
droptabletb
dropfunctiondbo.f_strUnite
go
/*
idvalue
----------------------
1aa,bb
2aaa,bbb,ccc
（所影响的行数为2行）
*/
--===================================================================================
2.新的解决方法（在sqlserver2005中用OUTERAPPLY等解决。
）
createtabletb（idint,valuevarchar（10））
insertintotbvalues（1,'aa'）
insertintotbvalues（1,'bb'）
insertintotbvalues（2,'aaa'）
insertintotbvalues（2,'bbb'）
insertintotbvalues（2,'ccc'）
go
--查询处理
SELECT*FROM（SELECTDISTINCTidFROMtb）AOUTERAPPLY（
SELECT[values]=STUFF（REPLACE（REPLACE（
（
SELECTvalueFROMtbN
WHEREid=A.id
FORXMLAUTO
）,'',''）,1,1,''）
）N
droptabletb
/*
idvalues
----------------------
1aa,bb
2aaa,bbb,ccc
（2行受影响）
*/
--SQL2005中的方法2
createtabletb（idint,valuevarchar（10））
insertintotbvalues（1,'aa'）
insertintotbvalues（1,'bb'）
insertintotbvalues（2,'aaa'）
insertintotbvalues（2,'bbb'）
insertintotbvalues（2,'ccc'）
go
selectid,[values]=stuff（（select','+[value]fromtbtwhereid=tb.idforxmlpath（''））,1,1,''）
fromtb
groupbyid
/*
idvalues
-------------------------------
1aa,bb
2aaa,bbb,ccc
（2row（s）affected）
*/
droptabletb
--处理并发
1如何锁一个表的某一行
A连接中执行
SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD
begintran
select*fromtablenamewith（rowlock）whereid=3
waitfordelay'00:
00:
05'
committran
B连接中如果执行
updatetablenamesetcolname='10'whereid=3--则要等待5秒
updatetablenamesetcolname='10'whereid<>3--可立即执行
2锁定数据库的一个表
SELECT*FROMtableWITH（HOLDLOCK）
注意:
锁定数据库的一个表的区别
SELECT*FROMtableWITH（HOLDLOCK）
其他事务可以读取表，但不能更新删除
SELECT*FROMtableWITH（TABLOCKX）
其他事务不能读取表,更新和删除
锁定记录,只允许单用户修改的例子
createtable#锁表（编号int）
--代码：
ifexists（select1from编号='你的编号'）
return
insert#锁表values（'你的编号'）
.....你处理的代码
delete#锁表where编号='你的编号'
--------------------------------------
--为了防止死锁,建议加时间:
createtable#锁表（编号int,时间datetime）
--代码：
ifexists（select1from编号='你的编号'
升级会员 