SQL Server時間粒度系列----第8節位運算以及設置日曆數據表節假日標誌詳解

来源:http://www.cnblogs.com/dzy863/archive/2016/01/18/5139188.html
-Advertisement-
Play Games

本文目錄列表：1、位運算2、設置日曆數據表節假日標誌3、總結語4、參考清單列表位運算SQL Server支持的按位運算符有三個，分別為：按位與（&）、按位或（|）、按位異或（^)。位運算符用於int、smallint或tinyint數據，目前SQL Server能支持的按位運算的最大整數類型為Int...


本文目錄列表： 1、位運算 2、設置日曆數據表節假日標誌 3、總結語 4、參考清單列表   位運算   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 0
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代碼如下： 
  1 IF OBJECT_ID(N'dbo.usp_Calendar_WorkDaySet', 'P') IS NOT NULL
  2 BEGIN
  3     DROP PROCEDURE dbo.usp_Calendar_WorkDaySet;
  4 END
  5 GO
  6  
  7 --==================================
  8 -- 功能: 設置指定月份的工作日標誌
  9 -- 說明: 具體實現闡述 
 10 -- 作者: XXX
 11 -- 創建: yyyy-MM-dd
 12 -- 修改: yyyy-MM-dd XXX 修改內容描述
 13 --==================================
 14  
 15 CREATE PROCEDURE usp_Calendar_WorkDaySet
 16 (
 17     @intMonths INT,                                -- 指定的日期月數
 18     @intWorkDayValueSum INT,                    -- 指定的日期月數的所有工作日標誌值之和
 19     @bitIsUseDefault BIT = 0,                    -- 是否使用預設設置，1：使用預設設置（周一到周五為工作日，周六和周日非工作日），0：基於指定的日期月數的所有工作日標誌值之和來設置
 20  
 21     -- 方便記錄用戶操作日誌
 22     --@chvnUser NVARCHAR(20),                        -- 指定的用戶
 23     --@intUserID INT,                                -- 指定的用戶ID
 24     --@chvUserIP VARCHAR(40),                        -- 指定的用戶IP
 25     --@chvnUserFrom NVARCHAR(30),                    -- 指定的用戶位置
 26  
 27     @chvnErrMsg NVARCHAR(100) OUTPUT            -- 錯誤異常消息字元串
 28 )
 29     --$Encode$--
 30 AS
 31 BEGIN
 32     SET NOCOUNT ON;
 33  
 34     SET @intMonths = dbo.ufn_GetValidDateNum(@intMonths);
 35  
 36     IF @intWorkDayValueSum IS NULL OR @intWorkDayValueSum < 0
 37     BEGIN
 38         SET @intWorkDayValueSum = 0;
 39     END
 40  
 41     SET @chvnErrMsg = N'';
 42  
 43     DECLARE @tintResultValue AS TINYINT;
 44     SET @tintResultValue = 1; -- 預設存在錯誤
 45  
 46     DECLARE
 47         @dtmNow AS DATETIME,
 48         @intDays AS INT;
 49     SELECT
 50         @dtmNow = GETDATE(),
 51         @intDays = dbo.ufn_Days(@dtmNow);
 52  
 53     IF @intMonths < dbo.ufn_Months(@dtmNow)
 54     BEGIN
 55         SET @chvnErrMsg = N'不能設置小於當前月份的工作日標誌。';
 56  
 57         RETURN @tintResultValue;
 58     END
 59  
 60     DECLARE
 61         @WorkDayValueSum AS INT,
 62         @DayCount AS INT;
 63     SELECT
 64         @WorkDayValueSum = 0,
 65         @DayCount = 0;
 66  
 67     SELECT
 68         @WorkDayValueSum = SUM(POWER(2, [DayOfMonth] - 1))
 69         ,@DayCount = COUNT(1)
 70     FROM dbo.Calendar
 71     WHERE Months = @intMonths
 72         AND [Days] >= @intDays + 1;
 73  
 74     IF @DayCount = 0 OR @WorkDayValueSum = 0
 75     BEGIN
 76         SET @chvnErrMsg = N'日曆數據表不存在滿足條件的數據。';
 77  
 78         RETURN @tintResultValue;
 79     END
 80  
 81     IF @intWorkDayValueSum = @WorkDayValueSum
 82     BEGIN
 83         SET @tintResultValue = 0;
 84  
 85         RETURN @tintResultValue;
 86     END
 87  
 88     DECLARE    @intRowCount AS INT;
 89     SELECT @intRowCount = 0;
 90  
 91     BEGIN TRY
 92         IF @bitIsUseDefault = 0
 93         BEGIN
 94             UPDATE Calendar
 95             SET WorkdayFlag = POWER(2, [DayOfMonth] - 1) & @intWorkDayValueSum
 96             WHERE Months = @intMonths 
 97                 AND [Days] >= @intDays + 1;
 98         END
 99         ELSE
100         BEGIN
101             UPDATE Calendar
102             SET WorkdayFlag = CASE WHEN dbo.ufn_DayOfWeek(CalendarDate) <= 5 THEN 1 ELSE 0 END
103             WHERE Months = @intMonths 
104                 AND [Days] >= @intDays + 1;
105         END
106  
107         SET @intRowCount = @@ROWCOUNT;
108  
109         SET @tintResultValue = 0;
110     END TRY
111     BEGIN CATCH        
112         SET @chvnErrMsg = N'設置指定月的工作日標誌發生錯誤。';
113  
114         RETURN @tintResultValue;
115     END CATCH
116  
117     RETURN @tintResultValue;
118 END
119 GO
120  
121 -- Test Code
122 DECLARE
123     @intMonths AS INT,
124     @intWorkDayValueSum AS INT,
125     @bitIsUseDefault AS BIT,
126     @chvnErrMsg AS NVARCHAR(100),
127     @tintResultVaule AS TINYINT;
128 SELECT
129     @intMonths = 0,                                            -- int
130     @intWorkDayValueSum = 0,                                -- int
131     @bitIsUseDefault = NULL,                                -- bit
132     @chvnErrMsg = N'',                                        -- nvarchar(100)
133     @tintResultVaule = 1;                                    -- tinyint
134  
135 EXEC @tintResultVaule = dbo.usp_Calendar_WorkDaySet
136     @intMonths = @intMonths,                                -- int
137     @intWorkDayValueSum = @intWorkDayValueSum,                -- int
138     @bitIsUseDefault = @bitIsUseDefault,                    -- bit
139     @chvnErrMsg = @chvnErrMsg OUTPUT                        -- nvarchar(100)
140  
141 SELECT @chvnErrMsg AS 'Error Message'
142     ,@tintResultVaule AS 'Return Value';
143 GO
144  
145 -- Test Code
146 -- 2016-02月份
147 -- 根據國家節假日獲取的工作日標誌值和以及工作日總數
148 SELECT 
149     WorkDayValueSum = SUM(T.WorkDayFlag2 * POWER(2, T.[DayOfMonth] - 1))
150     ,WorkDayCount = SUM(T.WorkDayFlag2 * 1)
151 FROM (
152     SELECT 
153         Months
154         ,[DayOfMonth]
155         ,WorkDayFlag
156         ,WorkDayFlag2 = CASE 
157                             WHEN [DayOfMonth] = 6 THEN 1
158                             WHEN [DayOfMonth]  BETWEEN 7 AND 12 THEN 0
159                             WHEN [DayOfMonth] = 14 THEN 1
160                             ELSE WorkDayFlag END
161     FROM dbo.Calendar
162     WHERE Months = dbo.ufn_Months('2016-02-01')
163 ) AS T
164 GO
165  
166 DECLARE
167     @intMonths AS INT,
168     @intWorkDayValueSum AS INT,
169     @bitIsUseDefault AS BIT,
170     @chvnErrMsg AS NVARCHAR(100),
171     @tintResultVaule AS TINYINT;
172 SELECT
173     @intMonths = dbo.ufn_Months('2016-02-01'),                -- int
174     @intWorkDayValueSum = 333963327,                        -- int
175     @bitIsUseDefault = 0,                                    -- bit
176     @chvnErrMsg = N'',                                        -- nvarchar(100)
177     @tintResultVaule = 1;                                    -- tinyint
178  
179 EXEC @tintResultVaule = dbo.usp_Calendar_WorkDaySet
180     @intMonths = @intMonths,                                -- int
181     @intWorkDayValueSum = @intWorkDayValueSum,                -- int
182     @bitIsUseDefault = @bitIsUseDefault,                    -- bit
183     @chvnErrMsg = @chvnErrMsg OUTPUT                        -- nvarchar(100)
184  
185 SELECT @chvnErrMsg AS 'Error Message'
186     ,@tintResultVaule AS 'Return Value';
187 GO
188  
189

 

測試以上存儲的功能效果，如下圖：   查詢201602月份的日曆數據，如下圖：   獲取工作日標誌的存儲過程，T-SQL代碼如下： 
 1 IF OBJECT_ID(N'dbo.usp_Calendar_WeekDayGet', 'P') IS NOT NULL
 2 BEGIN
 3     DROP PROCEDURE dbo.usp_Calendar_WeekDayGet
 4 END
 5 GO
 6  
 7 --==================================
 8 -- 功能: 獲取滿足條件的日期月數的工作日值和工作日總數
 9 -- 說明: 具體實現闡述 
10 -- 作者: XXX
11 -- 創建: yyyy-MM-dd
12 -- 修改: yyyy-MM-dd XXX 修改內容描述
13 --==================================
14 CREATE PROCEDURE dbo.usp_Calendar_WeekDayGet
15 (
16     @intStartMonths INT,                -- 指定的開始日期月數
17     @intEndMonths INT                    -- 指定的結束日期月數
18 )
19     --$Encode$--
20 AS 
21 BEGIN
22     SET NOCOUNT ON;
23  
24     SET @intStartMonths = dbo.ufn_GetValidDateNum(@intStartMonths);
25     SET @intEndMonths = dbo.ufn_GetValidDateNum(@intEndMonths);
26  
27     IF @intStartMonths > @intEndMonths
28     BEGIN
29         DECLARE @intTemp AS INT;
30         SET @intTemp = @intStartMonths;
31         SET @intStartMonths = @intEndMonths;
32         SET @intEndMonths = @intTemp;
33     END
34  
35     SELECT
36         Months
37         ,WorkDayValueSum = ISNULL(SUM(WorkDayFlag * POWER(2, [DayOfMonth] - 1)), 0)
38         ,WorkDayCount = ISNULL(SUM(WorkDayFlag * 1), 0)
39     FROM dbo.Calendar
40     WHERE Months BETWEEN @intStartMonths AND @intEndMonths
41     GROUP BY Months;
42 END
43 GO
44  
45 -- Test Code
46 DECLARE
47     @intStartMonths AS INT,
48     @intEndMonths AS INT,
49     @tintResultValue AS TINYINT;
50  
51 SELECT
52     @intStartMonths = dbo.ufn_Months('2015-06-01'),
53     @intEndMonths = dbo.ufn_Months('2016-03-02'),
54     @tintResultValue = 1;    -- 預設範圍值
55  
56 EXEC @tintResultValue = dbo.usp_Calendar_WeekDayGet
57     @intStartMonths = @intStartMonths,        -- int
58     @intEndMonths = @intEndMonths;            -- int
59  
60 SELECT @tintResultValue AS 'Return Value (1:Have Error,0:No Error)'
61 GO

 

  測試以上存儲的功能效果，如下圖：   總結語       本文提起了SQL Server的按位運算符，重點講解了日曆數據表中的工作日標誌的設置的處理方法，基於集合的處理方法，結合按位與運算符來處理的方法。   參考清單列表 1、https://msdn.microsoft.com/zh-cn/library/ms176122(v=sql.90).aspx 2、https://msdn.microsoft.com/zh-cn/library/ms188725(v=sql.90).aspx 3、https://msdn.microsoft.com/zh-cn/library/ms174965(v=sql.90).aspx 4、https://msdn.microsoft.com/zh-cn/library/ms186714(v=sql.90).aspx 5、https://msdn.microsoft.com/zh-cn/library/ms190277(v=sql.90).aspx
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ORACLE學習之PL/SQL編程——本地動態SQL
    目錄讀前註意與概述動態SQL簡介使用EXCUTE IMMEDIATE語句處理DDL和DCL語句處理DML語句處理單行查詢處理多行查詢在動態SQL中使用批量綁定讀前註意與概述 當編寫PL/SQL塊時，靜態SQL語句只能完成一些固定任務。為了使得PL/SQL塊可以靈活的處理SQL語句，需要使用動態SQ....
  • 開窗函數之OVER子句、PARTITION BY 子句
    開窗函數與聚合函數一樣，都是對行的集合組進行聚合計算。它用於為行定義一個視窗（這裡的視窗是指運算將要操作的行的集合），它對一組值進行操作，不需要使用GROUP BY子句對數據進行分組，能夠在同一行中同時返回基礎行的列和聚合列。開窗函數的調用格式為：函數名(列) OVER(選項)。第一大類：聚合開窗函...
  • 集算器協助java處理多樣性數據源之HDFS
    Java通過Hadoop提供的API訪問HDFS不算困難，但針對其上文件的計算就比較麻煩。比如分組、過濾、排序等計算，用java來實現都比較複雜。集算器esproc能很好地協助java解決計算問題，同時也封裝了HDFS的訪問，藉助esproc可以讓java加強HDFS上文件的計算能力，結構化半結構....
  • 根據資料庫輸出XML菜單
    USE[test_YTHH]GO/******Object:StoredProcedure[dbo].[usp_Print_SCC_Menu]ScriptDate:04/08/201311:21:23******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGO——...
  • How to solve a login problem of SQL Server?
    Sometimes when we attempting to login the SQL Server 20xx Management Studio, when we type in the correct password, it fails with: "A network-related ....
  • MongoDB 初見指南
    技術若只如初見，那麼還會踩坑麽? 在系統引入 MongoDB 也有幾年了，一開始是因為 MySQL 中有單表記錄增長太快（每天幾千萬條吧）容易拖慢 MySQL 的主從複製。而這類數據增長迅速的流水錶，對數據一致性也沒那麼高要求，而且業務上也不需要關聯查詢它，就考慮分出去。為什麼是 M...
  • MyCAT實現MySQL的讀寫分離
    在MySQL中間件出現之前，對於MySQL主從集群，如果要實現其讀寫分離，一般是在程式端實現，這樣就帶來一個問題，即資料庫和程式的耦合度太高，如果我資料庫的地址發生改變了，那麼我程式端也要進行相應的修改，如果資料庫不小心掛掉了，則同時也意味著程式的不可用，而這對很多應用來說，並不能接受。引入MySQ...
  • 使用ROW_NUMBER進行的快速分頁
    Linq分頁的方法用到Skip()，Take()。然而，用SQL腳本進行分頁如何寫呢？首先我們可以通過ROW_NUMBER() OVER進行排序並得到一個帶序號的視圖，再通過序號確定要查找的分頁數據例： 1 DECLARE @pageSize INT ; 2 DECLARE @pageIndex I...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類