本文目录列表：
位运算
SQL Server支持的按位运算符有三个，分别为：按位与（&）、按位或（|）、按位异或（^)。位运算符用于 int、smallint 或 tinyint 数据，目前SQL Server能支持的按位运算的最大整数类型为Int（4字节整数）。有关以上三个按位运算符的详细使用，请参考微软的SQL Server的联机帮助。
设置日期数据表节假日标志
上篇博文（日历数据表详解）中，总结出来一个日历数据表，该表的字段列（WorkDayFlag bit)表示是否工作日。默认设置周一到周五为工作日，周六和周日为非工作日的。但是国家法定节假日有时候会占用默认工作日（周一到周五中一天或若干天）的，这就要涉及设置工作日的功能。这个大家很显然能想到一条日历数据记录的进行设置，也是基于迭代或游标的方法。
基于迭代或游标的方法仔细想想确实存在性能问题的，一个月至少有28天，最多的有31天，如果设置一个月中每天的工作日标志字段列值，需要很多次的数据库连接资源的，即便共享一次数据库连接，多次执行的也是存在性能问题的。那如何解决常规方式存在的性能问题呢？
我们先不说解决方案，我们先从每月包含的最大天数31来说起。我们再来看SQL Server 提供的数据类型int，这是个有符号的4字节整数，共计32位，第32位为符号位，对于非负数该符号位为0，对于负数该符号位为1。非负整数的int只有31位来存储数据的；每个月最大天数是31天，这两者确实很巧合吧。如果我们将一个月的每一天分别对应一个int的每一位，从第一天到最后一天分别对应int的第一位到第31位，如以下表格：
| 月内日索引（从1开始计数） | int位索引（从0开始计数） |
|
1
|
|
|
2
|
1
|
|
3
|
2
|
| …… |
……
|
|
28
|
28
|
|
29
|
28 |
|
30
|
29
|
| 31 |
30
|
月内日索引和int索引相差1，这个很容易发现的。
既然知道了月内日索引( 我们设位变量DayOfMonth)和int位索引(我们设置变量为BitOfInt)的关系，两者之间的关系使用变量表示为：DayOfMonth - 1 = BitOfInt，那么我们就是用一个int值来保存一个月所有的工作日标志值（每个工作日标志值：1*power(2，DayOfMonth-1)， 整数的位值：1*power(2, BitOfInt)，这两者是相等的）之和（我们设置变量为WorkDayValueOfMonth），。这样就可以基于集合的方法来设置一个月的工作日标志。有了工作日标志值之和的int数值，那么如何分别设置每一天的工作日标志值呢？这个就要使用SQL Server提供的按位与运算符，如果(1*power(2，DayOfMonth-1)) & WorkDayValueOfMonth 的按位与的结果值为1，那么DayOfMonth所对应的的天则设置为了工作日；如果其结果为0，则设置为了非工作日。
基于集合的方法来设置一个月的每天的工作日标志，也不是每一天都可以设置的，我们设置工作日标志有关前提，所设置的工作日必须大于当前日（今天）的，小于当前日的我们不做设置，只能按照默认工作日设置（周一到周五为工作日，周六和周日为非工作日）。
既然有了设置工作日标志的方法，当然也有获取指定月的工作日值和和当前月的工作日的数量。根据以上我们提供两个存储过程，分别对应设置工作日标志或获取工作日标志的功能。
设置工作日标志的存储过程，T-SQL代码如下：
IF OBJECT_ID(N'dbo.usp_Calendar_WorkDaySet', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.usp_Calendar_WorkDaySet;
END
GO --==================================
-- 功能: 设置指定月份的工作日标志
-- 说明: 具体实现阐述
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
--================================== CREATE PROCEDURE usp_Calendar_WorkDaySet
(
@intMonths INT, -- 指定的日期月数
@intWorkDayValueSum INT, -- 指定的日期月数的所有工作日标志值之和
@bitIsUseDefault BIT = 0, -- 是否使用默认设置，1：使用默认设置（周一到周五为工作日，周六和周日非工作日），0：基于指定的日期月数的所有工作日标志值之和来设置 -- 方便记录用户操作日志
--@chvnUser NVARCHAR(20), -- 指定的用户
--@intUserID INT, -- 指定的用户ID
--@chvUserIP VARCHAR(40), -- 指定的用户IP
--@chvnUserFrom NVARCHAR(30), -- 指定的用户位置 @chvnErrMsg NVARCHAR(100) OUTPUT -- 错误异常消息字符串
)
--$Encode$--
AS
BEGIN
SET NOCOUNT ON; SET @intMonths = dbo.ufn_GetValidDateNum(@intMonths); IF @intWorkDayValueSum IS NULL OR @intWorkDayValueSum < 0
BEGIN
SET @intWorkDayValueSum = 0;
END SET @chvnErrMsg = N''; DECLARE @tintResultValue AS TINYINT;
SET @tintResultValue = 1; -- 默认存在错误 DECLARE
@dtmNow AS DATETIME,
@intDays AS INT;
SELECT
@dtmNow = GETDATE(),
@intDays = dbo.ufn_Days(@dtmNow); IF @intMonths < dbo.ufn_Months(@dtmNow)
BEGIN
SET @chvnErrMsg = N'不能设置小于当前月份的工作日标志。'; RETURN @tintResultValue;
END DECLARE
@WorkDayValueSum AS INT,
@DayCount AS INT;
SELECT
@WorkDayValueSum = 0,
@DayCount = 0; SELECT
@WorkDayValueSum = SUM(POWER(2, [DayOfMonth] - 1))
,@DayCount = COUNT(1)
FROM dbo.Calendar
WHERE Months = @intMonths
AND [Days] >= @intDays + 1; IF @DayCount = 0 OR @WorkDayValueSum = 0
BEGIN
SET @chvnErrMsg = N'日历数据表不存在满足条件的数据。'; RETURN @tintResultValue;
END IF @intWorkDayValueSum = @WorkDayValueSum
BEGIN
SET @tintResultValue = 0; RETURN @tintResultValue;
END DECLARE @intRowCount AS INT;
SELECT @intRowCount = 0; BEGIN TRY
IF @bitIsUseDefault = 0
BEGIN
UPDATE Calendar
SET WorkdayFlag = POWER(2, [DayOfMonth] - 1) & @intWorkDayValueSum
WHERE Months = @intMonths
AND [Days] >= @intDays + 1;
END
ELSE
BEGIN
UPDATE Calendar
SET WorkdayFlag = CASE WHEN dbo.ufn_DayOfWeek(CalendarDate) <= 5 THEN 1 ELSE 0 END
WHERE Months = @intMonths
AND [Days] >= @intDays + 1;
END SET @intRowCount = @@ROWCOUNT; SET @tintResultValue = 0;
END TRY
BEGIN CATCH
SET @chvnErrMsg = N'设置指定月的工作日标志发生错误。'; RETURN @tintResultValue;
END CATCH RETURN @tintResultValue;
END
GO -- Test Code
DECLARE
@intMonths AS INT,
@intWorkDayValueSum AS INT,
@bitIsUseDefault AS BIT,
@chvnErrMsg AS NVARCHAR(100),
@tintResultVaule AS TINYINT;
SELECT
@intMonths = 0, -- int
@intWorkDayValueSum = 0, -- int
@bitIsUseDefault = NULL, -- bit
@chvnErrMsg = N'', -- nvarchar(100)
@tintResultVaule = 1; -- tinyint EXEC @tintResultVaule = dbo.usp_Calendar_WorkDaySet
@intMonths = @intMonths, -- int
@intWorkDayValueSum = @intWorkDayValueSum, -- int
@bitIsUseDefault = @bitIsUseDefault, -- bit
@chvnErrMsg = @chvnErrMsg OUTPUT -- nvarchar(100) SELECT @chvnErrMsg AS 'Error Message'
,@tintResultVaule AS 'Return Value';
GO -- Test Code
-- 2016-02月份
-- 根据国家节假日获取的工作日标志值和以及工作日总数
SELECT
WorkDayValueSum = SUM(T.WorkDayFlag2 * POWER(2, T.[DayOfMonth] - 1))
,WorkDayCount = SUM(T.WorkDayFlag2 * 1)
FROM (
SELECT
Months
,[DayOfMonth]
,WorkDayFlag
,WorkDayFlag2 = CASE
WHEN [DayOfMonth] = 6 THEN 1
WHEN [DayOfMonth] BETWEEN 7 AND 12 THEN 0
WHEN [DayOfMonth] = 14 THEN 1
ELSE WorkDayFlag END
FROM dbo.Calendar
WHERE Months = dbo.ufn_Months('2016-02-01')
) AS T
GO DECLARE
@intMonths AS INT,
@intWorkDayValueSum AS INT,
@bitIsUseDefault AS BIT,
@chvnErrMsg AS NVARCHAR(100),
@tintResultVaule AS TINYINT;
SELECT
@intMonths = dbo.ufn_Months('2016-02-01'), -- int
@intWorkDayValueSum = 333963327, -- int
@bitIsUseDefault = 0, -- bit
@chvnErrMsg = N'', -- nvarchar(100)
@tintResultVaule = 1; -- tinyint EXEC @tintResultVaule = dbo.usp_Calendar_WorkDaySet
@intMonths = @intMonths, -- int
@intWorkDayValueSum = @intWorkDayValueSum, -- int
@bitIsUseDefault = @bitIsUseDefault, -- bit
@chvnErrMsg = @chvnErrMsg OUTPUT -- nvarchar(100) SELECT @chvnErrMsg AS 'Error Message'
,@tintResultVaule AS 'Return Value';
GO
测试以上存储的功能效果，如下图：
查询201602月份的日历数据，如下图：
获取工作日标志的存储过程，T-SQL代码如下：
IF OBJECT_ID(N'dbo.usp_Calendar_WeekDayGet', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.usp_Calendar_WeekDayGet
END
GO --==================================
-- 功能: 获取满足条件的日期月数的工作日值和工作日总数
-- 说明: 具体实现阐述
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
--==================================
CREATE PROCEDURE dbo.usp_Calendar_WeekDayGet
(
@intStartMonths INT, -- 指定的开始日期月数
@intEndMonths INT -- 指定的结束日期月数
)
--$Encode$--
AS
BEGIN
SET NOCOUNT ON; SET @intStartMonths = dbo.ufn_GetValidDateNum(@intStartMonths);
SET @intEndMonths = dbo.ufn_GetValidDateNum(@intEndMonths); IF @intStartMonths > @intEndMonths
BEGIN
DECLARE @intTemp AS INT;
SET @intTemp = @intStartMonths;
SET @intStartMonths = @intEndMonths;
SET @intEndMonths = @intTemp;
END SELECT
Months
,WorkDayValueSum = ISNULL(SUM(WorkDayFlag * POWER(2, [DayOfMonth] - 1)), 0)
,WorkDayCount = ISNULL(SUM(WorkDayFlag * 1), 0)
FROM dbo.Calendar
WHERE Months BETWEEN @intStartMonths AND @intEndMonths
GROUP BY Months;
END
GO -- Test Code
DECLARE
@intStartMonths AS INT,
@intEndMonths AS INT,
@tintResultValue AS TINYINT; SELECT
@intStartMonths = dbo.ufn_Months('2015-06-01'),
@intEndMonths = dbo.ufn_Months('2016-03-02'),
@tintResultValue = 1; -- 默认范围值 EXEC @tintResultValue = dbo.usp_Calendar_WeekDayGet
@intStartMonths = @intStartMonths, -- int
@intEndMonths = @intEndMonths; -- int SELECT @tintResultValue AS 'Return Value (1:Have Error,0:No Error)'
GO
测试以上存储的功能效果，如下图：
总结语
本文提起了SQL Server的按位运算符，重点讲解了日历数据表中的工作日标志的设置的处理方法，基于集合的处理方法，结合按位与运算符来处理的方法。
参考清单列表