欲查詢2013-09-16至2013-09-18此段時間之休假(公出)人員,符合條件者如下表中A01、A03及A04等3人,由於符合查詢條件組合繁多,難以撰寫符合各種狀況的查詢條件,尤其以A01已超越查詢區間,此類問題應嘗試以反向思考模式,以數位邏輯設計中之狄摩根定理(DeMorgan’s Theorem)進行簡化,可歸納出非常簡單之結論,推論說明如下。
以下列出狄摩根定理(DeMorgan’s Theorem),以供參閱並自行推論。
狄摩根定理(DeMorgan’s Theorem):
並將執行步驟整理如下:
步驟
|
圖示
|
條件式
|
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:不符合查詢條件之組合()
不符合查詢條件包含兩個區域,大於查詢上界時間(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:排除不符合條件者(負負為正,)
排除符合前一步驟中之資料,依狄摩根定理將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)
沒有留言:
張貼留言