【网学网提醒】：网学会员,鉴于大家对SQL一些小技巧十分关注，会员在此为大家搜集整理了“SQL一些小技巧”一文，供大家参考学习!
ThesehasbeenpickedupfromthreadwithinsqljunkiesForumssqljunkies/">sqljunkies
Problem
TheproblemisthatIneedtorounddifferently(byhalves)
Example:4.24roundsto4.00,but4.26roundsto4.50.
4.74roundsto4.50and4.76roundsto5.00
Solution
declare@tfloat
set@t=100.74
selectround(@t*2.0,0)/2
Problem
I'mwritingafunctionthatneedstotakeinacommaseperatedlistandusitinawhereclause.Theselectwouldlooksomethinglikethis:
select*frompeoplewherefirstnamein('larry','curly','moe')
Solution
usenorthwind
go
declare@xVarvarchar(50)
set@xVar='anne,janet,nancy,andrew,robert'
select*fromemployeeswhere@xVarlike'%'+firstname+'%'
Problem
Needasimplepagingsqlcommand
Solution
usenorthwind
go
select*fromproductsa
where(selectcount(*)fromproductsbwherea.productid>=b.productid)between15and16
Problem
Performcase-sensitivecomparisionwithinsqlstatementwithouthavingtousetheSETcommand
Solution
usenorhtwind
go
SELECT*FROMproductsASt1
WHEREt1.productnameCOLLATESQL_EBCDIC280_CP1_CS_AS='Chai'
--executethiscommandtogetdifferentcollatenaming
--select*from::fn_helpcollations()
Problem
Howtocallastoredprocedurelocatedinadifferentserver
Solution
SETNOCOUNTON
usemaster
go
EXECsp_addlinkedserver'172.16.0.22',N'SqlServer'
go
Execsp_link_publication@publisher='172.16.0.22',
@publisher_db='Northwind',
@publication='NorthWind',@security_mode=2,
@login='sa',@password='sa'
go
EXEC[172.16.0.22].northwind.dbo.CustOrderHist'ALFKI'
go
execsp_dropserver'172.16.0.22','droplogins'
GO
