作 者:楊先民
審 稿:張智凱
文章編號:
出刊日期:
前言
前期我們大概介紹了借書系統的 T-SQL程式,也順道帶到了在
第一期所提到的生產線流程,所以我們在每一期開始,都要再複
習一下這個生產線流程,也就是如下圖所示:

圖1:T-SQL指令撰寫的流程。
而上一期的借書系統,有來源與想看的結果,只是看中間如何拚湊出轉換的指令。
那我們來看看這次的情境吧!
出勤系統
這個問題是有個學員利用電子郵件問我的問題,說他已經想了兩個星期都想不出該如何把句子寫出來。最後我大概花了10分鐘左右的時間,完成解答。並和他說要把這個例子當成範例給大家練習參考。
這個問題是一個出勤系統,並附上一個 Excel表格,經由轉換後,成為下列的SQL語法:
USE tempdb
GO
/****** Object: Table [dbo].[刷卡記錄] Script Date: 07/27/2011 12:29:23 ******/
GO
CREATE TABLE [dbo].[刷卡記錄](
[門別] [nvarchar](255) NULL,
[狀態] [nvarchar](255) NULL,
[姓名] [nvarchar](255) NULL,
[部門] [nvarchar](255) NULL,
[員工編號] [float] NULL,
[日期] [date] NULL,
[時間] [time](7) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[刷卡記錄] ([門別], [狀態], [姓名], [部門], [員工編號], [日期], [時間]) VALUES (N' 自強2F門禁考勤(進) ', N' 卡號正確 ', N'黃惠敏', N' 英濟 ', 9720035, CAST(0xBE330B00 AS Date), CAST(0x07003CCEEF640000 AS Time))
INSERT [dbo].[刷卡記錄] ([門別], [狀態], [姓名], [部門], [員工編號], [日期], [時間]) VALUES (N' 自強3F門禁考勤(進) ', N' 卡號正確 ', N'黃惠敏', N' 英濟 ', 9720035, CAST(0xBE330B00 AS Date),, CAST(0x078053B7FC950000 AS Time))
範例程式碼1:出勤系統的資料
範例程式碼1是由系統產生的出勤資訊,每個員工在進出大門時,都會刷卡,而這些刷卡記錄就會被記錄下來,出勤的資料會看起來會如圖2:

圖2:出勤的明細資料
而他希望這個明細資料,在經由SQL的處理之後,會變成如下方的結果:
--
--列出每天員工上下班時間 (每天最早刷卡以及最晚刷卡資訊)
--結果會呈現類似以下:
-- 日期 姓名 時間 狀態
--2011-01-07 陳奕騰 10:29:46.0000000 上班
--2011-01-07 陳奕騰 17:23:00.0000000 下班
--2011-01-07 陳宥蓁 08:01:52.0000000 上班
--2011-01-07 陳宥蓁 08:49:59.0000000 下班
--2011-01-07 陳逸弘 07:49:56.0000000 上班
這個範例中,我們已經有資料來源(也就是圖2),也有想要看到的結果,要如何利用已知的SQL指令來達成需求呢?
首先,必需了解,原始資料的刷卡記錄是會重複的,如圖2的黃惠敏在同一天,就列出了重複的資料,因為不管是出門還是進門都要刷卡,就會產生這麼多的明細資料。但我們希望只列出黃姓員工「每天」上班與下班時的資料即可,並不需要全部的資料。
所以,資料處理的順序在於:1.去除重複的資料,2.取出上下班資訊
去除重複資料,可能大家心裡會想到用distinct或是group by來做。但要取出上下班資訊,就覺得不是那麼容易(雖然上班時間是該日期的最早刷卡時間,下班時間是該日期的最晚刷卡時間,但因為並沒有一個 SQL指令專門取出上班時間或下班時間的資料)。
如果此題的重點還是擺在如何利用group by或distinct去除重複資料,那麼,我想我也會和該學員一樣連想兩個星期,也想不出個所以然。
為何?因為group by要使用彙總函數,但以本例而言,是要使用count還是sum呢?似乎都沒有。但其實問題在於我們腦中想到的SQL指令不夠多,無法把整個 SQL指令全部納入思考,才會發生這種「想到去除重複列」,只有想到group by或distinct可以用的情況。
但其實只要先抓到上、下班時間,再解決重複列的問題,自然答案就出來了!
而如何取出上、下班時間呢?只要了解上班是該天刷卡的第一筆記錄,而下班是該天刷卡的最後一筆記錄,腦中就應該會浮現rank()這類的函數了吧?
是的,最後就是用rank()這個函數來解決,先抓上班資訊,再抓下班資訊,然後利用union關鍵字把他們合在一起。至於去除重複列,只要利用rank()函數中的partition by,就可以達到和group by相同的效果,所以撰寫出來的程式如範例程式碼2:
select 日期,姓名,時間,status as 狀態 from (
SELECT *,N'上班' as status FROM (
SELECT 日期,姓名,時間,rank() over (partition by 日期,,姓名 order by 時間) as ranks FROM dbo.刷卡記錄) AS T
WHERE T.RANKS =1
UNION
SELECT *,N'下班' as status FROM (
SELECT 日期,姓名,時間,rank() over (partition by 日期,,姓名 order by 時間 DESC) as ranks FROM dbo.刷卡記錄) AS T
WHERE T.RANKS =1) as b
order by 日期,姓名
範例程式碼2:出勤系統的答案
最後得到的結果如圖3:

圖3:計算上、下班出勤記錄的結果
當然,結果如上圖會呈現上班與下班的狀態,是另外再使用了次查詢所做的設定。
這樣便很順利地解決了一個困難的問題,不過,也讓我們了解到,撰寫SQL指令,就某些層面上來説,很像學習英文,背的單字要多且廣,到需要時,能順利運用出來的機率就會大增。如果在前面幾個章節中,你對於每個個別的SQL指令都很熟悉,也親自測試如何使用。則到最後綜合演練時,就能把之前所學習的SQL指令從腦海中取出,並按照解題順序將結果產生出來。
總結
希望本期能夠幫助你,在思考自已公司情境時,能順利找出所謂的「解題順序」,再配合已會的SQL指令,將答案完成。不過必需提醒各位,本例的刷卡資料是在同棟大樓的員工,也沒有在不同大樓流動的情況,如果情境稍微修改一下,人員在不同棟大樓,不同部門的裝卡資訊都會被記錄下來,請列出每天每個人的上下班資訊,這個問題的答案可能就會不同(其實也還好,只是以天為分組條件罷了)