SQL一些小技巧
站点：爱心种子小博士 关键字：SQL一些小技巧
|
SQL一些小技巧
These has been picked up from thread within sqljunkies Forums sqljunkies.com/">http://www.sqljunkies.com
Problem
The problem is that I need to round differently (by halves)
Example: 4.24 rounds to 4.00, but 4.26 rounds to 4.50.
4.74 rounds to 4.50 and 4.76 rounds to 5.00
Solution
declare @t float
set @t = 100.74
select round(@t * 2.0, 0) / 2
Problem
Im writing a function that needs to take in a comma seperated list and us it in a where clause. The select would look something like this:
select * from people where firstname in (larry,curly,moe)
Solution
use northwind
go
declare @xVar varchar(50)
set @xVar = anne,janet,nancy,andrew, robert
select * from employees where @xVar like % + firstname + %
Problem
Need a simple paging sql command
Solution
use northwind
go
select * from products a
where (select count(*) from products b where a.productid >= b.productid) between 15 and 16
Problem
Perform case-sensitive comparision within sql statement without having to use the SET command
Solution
use norhtwind
go
SELECT * FROM products AS t1
WHERE t1.productname COLLATE SQL_EBCDIC280_CP1_CS_AS = Chai
--execute this command to get different collate naming
--select * from ::fn_helpcollations()
Problem
How to call a stored procedure located in a different server
Solution
SET NOCOUNT ON
use master
go
EXEC sp_addlinkedserver 172.16.0.22,NSql Server
go
Exec sp_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
exec sp_dropserver 172.16.0.22, droplogins
GO
|
|
|
|
