SQLServer 中经常使用的语句

概述1.获取第10 到20条记录（uid列为自增长，不连续列） 法一： select * from (select row_number() over(order by uid asc) row_num,* from accounts) a where a.row_num>10 and a.row_num<20法二: select top 10 * from accounts where uid no

1.获取第10 到20条记录（uID列为自增长，不连续列）

法一：

select * from (select row_number() over(order by uID asc) row_num,* from accounts) a where a.row_num>10 and a.row_num<20

法二:

select top 10 * from accounts where uID not in (select top 10 uID from accounts order by uID) order by uID

2.SQL语句查询excel表

查询前需要先执行:

exec sp_configure 'show advanced options',1reconfigureexec sp_configure 'ad hoc distributed querIEs',1reconfigure

2003,2007,2010

2.1>.

SELECT * FROM OpenDataSource( 'Microsoft.ACE.olEDB.12.0','Data Source="D:\a.xlsx";User ID=admin;Password=;Extended propertIEs=Excel 12.0')...[Sheet1$]

2.2>.

SELECT * FROM OPENROWSET('Microsoft.ACE.olEDB.12.0','Excel 12.0;Database=d:\a.xlsx','SELECT * FROM [Sheet1$A1:D100]')

2.3>.

SELECT * FROM OPENROWSET('Microsoft.ACE.olEDB.12.0','SELECT * FROM [Sheet1$]')

2003,2007

SELECT * FROM OpenDataSource( 'Microsoft.ACE.olEDB.12.0','Data Source="D:\a.xlsx";User ID=admin;Password=;Extended propertIEs="Excel 12.0 Xml;HDR=YES;IMEX=1";')...[Sheet1$]

2003

SELECT *FROM OpenDataSource( 'Microsoft.Jet.olEDB.4.0','Data Source="d:\a.xls";User ID=admin;Password=;Extended propertIEs=Excel 8.0')...[Sheet1$]

执行查询后关闭:

exec sp_configure 'Ad Hoc distributed QuerIEs',0reconfigureexec sp_configure 'show advanced options',0reconfigure

3.随机获取10条数据:

select top 10 * from accounts order by newID()

4.按姓氏笔画排序:

Select top 20 * From accounts Order By turename Collate Chinese_PRC_stroke_ci_as asc

5.字段值加密

5.1>加密：pwdencrypt

print pwdencrypt('123456') --'123456'为明码

5.2>明码和加密字符串 比较 pwdcompare：如果是同一个返回：1 or：0

print pwdcompare('123456',0x010044F11ED6DBF06A7E77D9F905F72BA344FE75FCFF4E43FC3E)

6.获取某年某月 有多少天

思路:先利用DATEDIFF取得当前月的第一天，再将月份加一取得下月第一天，然后减去1分钟，再取日期的天数部分，即为当月最大日期，也即当月天数

CREATE FUNCTION DaysInMonth ( @date datetime ) Returns intASBEGIN RETURN Day(dateadd(mi,-1,DATEADD(m,DATEDIFF(m,@date)+1,0)))ENDeg:select dbo.DaysInMonth('2012-2-1') --结果:29

7.获取本周星期一的日期:

SELECT DATEADD(week,DATEDIFF(week,getdate()),0)

8.获取当前季度的第一天:

SELECT DATEADD(quarter,DATEDIFF(quarter,'2012-5-8'),0)

9.获取一个季度多少天:

declare @m tinyint,@time smalldatetimeselect @m=month(getdate()) select @m=case when @m between 1 and 3 then 1                        when @m between 4 and 6 then 4                        when @m between 7  and 9 then 7                        else 10 endselect @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01' select datediff(day,@time,dateadd(mm,3,@time))

10.获取一年中最后一天:

SELECT dateadd(mi,DATEADD(yy,DATEDIFF(yy,getdate())+1,0))

11. 将float转换成varchar(从excel里导入到数据库表经常忘记设置类型):转成decimal->再转成varchar即可
select convert(varchar(20),cast(convert(float,'1.58609e+010') as decimal))

12.跨库查询（连接查询两个不同服务器上的数据库信息）【和查询excel类似，只是数据源不同】

exec sp_configure 'show advanced options',1reconfigureexec sp_configure 'Ad Hoc distributed QuerIEs',1reconfigureselect * from accounts ainner joinOPENDATASOURCE(         'sqlolEDB','Data Source=192.168.13.99;User ID=sa;Password=123456'         ).TongjIDB.dbo.accounts bon a.turename=b.turenamewhere a.uID = 4076exec sp_configure 'Ad Hoc distributed QuerIEs',0reconfigure

总结

以上是内存溢出为你收集整理的SQLServer 中经常使用的语句全部内容，希望文章能够帮你解决SQLServer 中经常使用的语句所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。