SQL 一些小技巧
2024-07-21 02:06:10
供稿：網友
these has been picked up from thread within sqljunkies forums 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
i'm 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',n'sql 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
