|
- --新分页方法(效率较高)
- select * from sys.columns order by object_id offset 5 rows fetch next 3 rows only
- --可创建类似Oracle 的Sequence 序列便于多表共用一个序列
- create sequence sid as int start with 1 increment by 1 MaxValue 10 /*序列最大值*/ MinValue -2 /*序列最小值*/ Cycle /*可循环*/
- --序列修改(步长居然可以为负数)
- alter sequence sid restart with 3 increment by -1
- --获取序列值 如何获取当前值??
- declare @i int
- select @i = next value for sid
- print @i
- --字符串连接函数测试(任意长度,任意类型)
- select concat('SqlServer',2012,null,'Enterprise',getdate())
- --新增选择函数第一个参数从开始
- select choose(0, '1','2','3','4')
- select choose(3, '1','2','3','4')
- --新增判断函数
- select iif(1>2 ,'1',getdate())
- --新增月计算函数
- select EOMONTH(getdate()) as '本月最后一天'
- select EOMONTH(getdate(),1) as '下月最后一天'
- select EOMONTH(getdate(),-1) as '上月最后一天'
- --新增类型转换函数Parse Try_Convert Try_Parse
- select TRY_CONVERT(float,'test') -- 转换不出,返回null
- select TRY_CONVERT(float,'1')
- select Try_Parse('test' as datetime)
- select Try_Parse('2012-04-01' as datetime)
- --比较有用的分析函数LAG(错位比较) 同时可进行分区错位比较
- --下例数介绍如何时候column_id 前后两行数据相减,取代错位连接的麻烦
- select lag(column_id,1,0) over (PARTITION BY object_id order by object_id,column_id)
- from sys.columns
- --错位连接(取代RowNumber 函数错位连接)
- select object_id,column_id,LEAD(column_id,1,0) over (PARTITION BY object_id order by object_id,column_id) as NextColums
- from sys.columns
复制代码
|
|