T-SQL Enhancements
参考：英文，中文

1，WITH RESULT SETS
更改存储过程返回结果表的字段名类型
CREATE PROCEDURE Denali_WithResultSet  AS
BEGIN
    SELECT 1 as No,’Tsql’ Type, ‘WithResultSet’ AS Feature
    UNION ALL  SELECT 2 as No,’Tsql’ Type, ‘Throw’ AS Feature
    UNION ALL SELECT 3 as No,’Tsql’ Type, ‘Offset’ AS Feature
    UNION ALL SELECT 4 as No,’Tsql’ Type, ‘Sequence’ AS Feature
END
GO
EXEC Denali_WithResultSet
WITH RESULT SETS  (( No int,FeatureType varchar(50), FeatureName varchar(50) )   )

2，OFFSET and FETCH
offset过滤几行 fetch:取下面的几行
SELECT top 15 * FROM dbo.frmuser f(NOLOCK) order BY account
SELECT * FROM dbo.frmuser f(NOLOCK) order BY account OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
SELECT top 5 * FROM ( SELECT * ,ROW_NUMBER()OVER (ORDER BY account)n FROM frmuser f)m WHERE m.n>10

3，SEQUENCE
提供自增长，与identity不同的是不依赖与表，并且是全局性的 可不设置最大值,若到最大值时可自动循环。
CREATE SEQUENCE SeqRange  AS int   START WITH 1   INCREMENT BY 1     MINVALUE 1      MAXVALUE 5    CYCLE ;//循环自动
SELECT NEXT VALUE FOR dbo.SeqRange  S
ELECT NEXT VALUE FOR dbo.Seq;
 SELECT NEXT VALUE FOR dbo.Seq;

到达最大值后，可重置
ALTER SEQUENCE seq  RESTART WITH 1

赋值：

DECLARE @I INT

相关存储过程
DECLARE @first_value sql_variant,
@last_value sql_variant
EXEC sp_sequence_get_range @sequence_name = N’SeqRange’, @range_size = 4, @range_first_value = @first_value OUTPUT, @range_last_value = @last_value OUTPUT;
SELECT @first_value AS FirstNumber, @last_value as LastNumber

应用：订单号生成
可在表定义时使用，这样我们就能在插入之前获取ID
create table t1 ( orderID integer default (next value for dbo.TestID), OrderDesc varchar(50) )

4，FORCESEEK and FORCESCAN
forceseek:强制查询按某个索引查找
forecescan:强制查询按查个索引扫描

5，
EOMONTH：返回当月最后一天
CHOOSE:返回列表中的第几个值
Select Choose (2, 'January', 'February', 'March');
IIF:三值逻辑
SELECT IIF (@a > @b, 'TRUE', ‘FALSE’) AS Result;
CONCAT: 连接，可多种类型，如整型与字符串相加
SELECT CONCAT ('age:',1) AS Result;

OVER 增强
unbounded preceding：从头开始
CURRENT ROW：当前行
unbounded following：尾

如：
SELECT *
FROM (
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount,
last_value(orderdate) OVER( PARTITION BY bc.ID ORDER BY bo.OrderDate RANGE BETWEEN unbounded preceding AND unbounded following ) lastorderdate
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
)m
WHERE m.orderdate = m.lastorderdate
ORDER BY CustomerName,OrderDate

6，分析函数
lag:返回按某种排序相比当前行，上几行的字段 LAG (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)
SELECT TOP 10 Account,lag(account,1,'开始') OVER( ORDER BY account )
FROM dbo.frmuser
ORDER BY account

SELECT TOP 10 Account,lead(account+'aaa',1,'开始') OVER( ORDER BY account )
leag:返回按某种排序相比当前行，下几行的字段
LAST_VALUE 返回 SQL Server 2012 中有序值集中的最后一个值,这个集可用range指定 默认是从第一行到当前这一行:RANGE BETWEEN unbounded preceding AND CURRENT ROW FIRST_VALUE 开头一个值
–需求：取出客户的最近一次购买的订单号与订单金额

SELECT *
FROM (
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount,
last_value(orderdate) OVER( PARTITION BY bc.ID ORDER BY bo.OrderDate RANGE BETWEEN unbounded preceding AND unbounded following ) lastorderdate
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
)m
WHERE m.orderdate = m.lastorderdate
ORDER BY CustomerName,OrderDate

–原方法
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
AND bo.orderdate = ( SELECT MAX(OrderDate)
FROM dbo.bdOrder bo2(NOLOCK)
WHERE bo2.CustomerID = bo.CustomerID)
ORDER BY CustomerName,OrderDate

【版权声明】
本站部分内容来源于互联网，本站不拥有所有权，不承担相关法律责任。如果发现本站有侵权的内容，欢迎发送邮件至masing@13sy.com 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。