SQL 一些小技巧
来源:互联网 发布:银联数据查询api接口 编辑:程序博客网 时间:2024/05/01 23:35
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
- SQL 一些小技巧
- SQL一些小技巧
- SQL一些小技巧
- SQL一些小技巧
- SQL一些小技巧
- SQL Server的一些小技巧
- SQL Server的一些小技巧
- 一些小技巧C# 和SQL
- 关于sql优化的一些小技巧
- sql server 中的一些小技巧
- 书写SQL语句的一些小技巧
- DBA日常维护中执行SQL的一些小技巧
- javascript一些小技巧
- 一些小技巧汇总
- SQLServer一些小技巧
- 一些小技巧
- 一些小技巧
- 一些小技巧
- 如果有这样的女孩对我
- 随键而想_VC是这样练成的
- 如何用正确的方法来写出质量好的软件的75条体会[收藏]
- ASC II 完整码表及简介
- 一个女生的恶毒情书
- SQL 一些小技巧
- 在.NET环境下如何给程序嵌入资源文件
- 使用COM内建IStream对象
- 经典--鱼和水说
- 听刘如鸿先生《如何设计具有可扩展性功能的软件架构》感想
- 今天blog开张
- 网络最经典命令行--网络安全工作者的必杀技
- 喜欢我 就别再问我有没有女朋友
- 莲藕薏米排骨汤