欲查詢2013-09-16至2013-09-18此段時間之休假(公出)人員,符合條件者如下表中A01、A03及A04等3人,由於符合查詢條件組合繁多,難以撰寫符合各種狀況的查詢條件,尤其以A01已超越查詢區間,此類問題應嘗試以反向思考模式,以數位邏輯設計中之狄摩根定理(DeMorgan’s Theorem)進行簡化,可歸納出非常簡單之結論,推論說明如下。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDqW8CoDh3JlCDUJbJHPTqGxiM_xYksKK19PXkOz6DwBMNvQedfSzvjCWOBijz1-5asQ73XKCljDjE9ev5fcZR2vVPhX5PpQRpyOlUf9cXUHGqa24_TMzdh42od5SZSnfW9tOe-pwVGow/s640/2018-02-27_202154.png)
以下列出狄摩根定理(DeMorgan’s Theorem),以供參閱並自行推論。
狄摩根定理(DeMorgan’s Theorem):
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlNhv6Aou73fkuSoSqYM7olKHpo6J1WpD-o8eBPpbmFpA2U-4mzMK4gUmQEZA7myQ_aOKJ9_is2oKqMF0lhmUC-jspKY4snB36lc7e2xQVxLlY_6wsBEi6UFwUdO-gLDKBkp6q7TiOdC8/s1600/2018-02-27_221944.png)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhi92xke-ALyBMpk48Wpxhl8YOCY8cfLJHMpw6_QQj-utB5OLx4imyjl7RdpuiU7VWKROz0UQw7_eyDIauVNcH5JJi_lR3PCQ67NWA3AQV00T3FfI1OPx8LZxDsisTPn-eaB0s5FsQgeAk/s1600/2018-02-27_222046.png)
並將執行步驟整理如下:
步驟
|
圖示
|
條件式
|
1.不符合
查詢條件
|
![]() |
DayOff.EDate < Query.SDate --u
OR DayOff.SDate > Query.EDate --v
(即:
![]() |
2.排除
不符合
|
![]() |
DayOff.EDate >= Query.SDate
--u
AND DayOff.SDate <= Query.EDate --v
(即:
![]() |
Step 01:不符合查詢條件之組合(
)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIlFedv6GXvEmWFJ0uqoKJw_peC5VQKJTf0LLu87wlnVLHxePOW6YNysv_QSWUTAaQI05ZNHTTnygKThBlHAltWWU5In84Fy38Wj8TnokBk_FokgfyxNCfEIyskmKOoq2vkRJit2XWQL0/s1600/2018-02-27_231808.png)
不符合查詢條件包含兩個區域,大於查詢上界時間(UBound,EDate)及小於查詢下界時間(LBound,SDate)。SQL及查詢結果,如下。請注意,查詢應包含上界(結束)當天日期,因此查詢條件應採用次日日期而非當日。
SQL
|
執行結果
|
SELECT Emp_No
, CONVERT(CHAR(16), SDate, 120) SDate
, CONVERT(CHAR(16), EDate, 120) SDate
FROM #DayOff O
WHERE 1=0
OR O.EDate < '2013-09-16' --(1)
OR O.SDate > DATEADD(dd,1,'2013-09-18')--(2)
|
![]() |
Step
02:排除不符合條件者(負負為正,
)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEip49OVY9kbSREcz-b5bceJuMVW415IYKZ5luqH7rUmWiQphpM4lX1B-fYlRaJbHOQz9abpokJn2snpXkGNKKbjJv-y9fI7Pa84_GeTtFUbZoBWC2eilXWhueXNtAm3DAwdiri48J9UNjg/s1600/2018-02-27_233835.png)
排除符合前一步驟中之資料,依狄摩根定理將SQL改寫,如下。查詢上界(結束)應採用次日日期,但包含次日該時點,因此公式應修正為小於(<)。
SQL
|
執行結果
|
SELECT Emp_No
, CONVERT(CHAR(16), SDate, 120) SDate
, CONVERT(CHAR(16), EDate, 120) SDate
FROM #DayOff O
WHERE 1=1
AND O.EDate >= '2013-09-16' --(1) AND O.SDate < DATEADD(dd,1,'2013-09-18')--(2) |
![]() |
注意:式(2)為開放式查詢條件,將以FULL TABLE SCAN方式而造成效能問題,可額外增加90天內篩選條件(產假56天)。
由前述運算式中,可歸納出區間查詢基本思考是為任一上限值(U.Bound)必大於另一下限值(L.Bound)。
最後,以常用(正向)思考模式難以處理區間查詢此類難題,可嘗試以反向思維解決,或者利用數學、數位邏輯(如:卡諾圖)或演算法等概念進行歸維簡化,相信應可找到另類且有效的方法。
SELECT * INTO #DayOff
FROM (VALUES ('A01'
, DATEADD(dd, 0, '2013-09-14 09:00')
, DATEADD(dd, 0, '2013-09-23 18:00') )
, ('A03', '2013-09-18 09:00', '2013-09-18 18:00')
, ('A04', '2013-09-18 13:00', '2013-09-23 18:00')
, ('A07', '2013-09-14 09:00', '2013-09-14 18:00')
, ('A08', '2013-09-23 09:00', '2013-09-23 18:00')
, ('A10', '2013-09-13 09:00', '2013-09-13 18:00')
) DayOff(Emp_No, SDate, EDate)
沒有留言:
張貼留言