Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
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
