iT邦幫忙

0

考勤系統SQL語法求救

code 2019-03-12 14:49:481902 瀏覽

各位大大好,最近在研究考勤系統,但是小弟SQL部分弱弱的,所以來這邊請高手救命

SQL語法
select b.pers_cod,b.pers_nam,a.attnd_dat,a.attnd_tim as ON_TIME from ATTND_TR a,pers_mn b where a.pers_ser=b.pers_ser and attnd_dat = to_date('2019-02-26', 'yyyy-mm-dd') and b.pers_cod='A001' order by b.pers_cod,a.attnd_tim asc

資料截圖
https://ithelp.ithome.com.tw/upload/images/20190312/20105113URd0s5AmeP.jpg

希望能排列成如下
https://ithelp.ithome.com.tw/upload/images/20190312/20105113W9aVLsFAJJ.jpg

有以下兩個問題請教各位大大

1.SQL語法該怎麼下才能顯示出像圖二這樣的結果
2.萬一打卡資料超過四筆,要如何橫向將所有時間都顯示出來?

再麻煩各位大大了,跪~~

看更多先前的討論...收起先前的討論...
恩..只想到TSQL
查看看 PIVOT 語法
code iT邦新手 5 級 ‧ 2019-03-12 15:49:19 檢舉
好的 會再研究看看 感謝兩位給予方向
石頭 iT邦研究生 3 級 ‧ 2019-03-13 00:07:58 檢舉
動態PIVOT + ROW_NUMBER 視窗函數 因該可以達成你的需求

https://dotblogs.com.tw/daniel/2019/02/15/174535
rogeryao iT邦高手 7 級 ‧ 2019-03-13 08:46:03 檢舉
to_date('2019-02-26', 'yyyy-mm-dd') ==> Oracle SQL ??
a.attnd_dat,a.attnd_tim 欄位型態 ??
code iT邦新手 5 級 ‧ 2019-03-13 09:54:04 檢舉
dog830228 : 謝謝,我會再研究研究

rogeryao : 這是oracle的資料庫沒錯,至於欄位型態因為我剛接觸所以不太清楚怎麼看

2 個回答

0
張小馬~
iT邦新手 3 級 ‧ 2019-03-13 09:23:40
最佳解答

歡迎加入SQL function的世界/images/emoticon/emoticon42.gif



1.SQL語法該怎麼下才能顯示出像圖二這樣的結果

https://ithelp.ithome.com.tw/upload/images/20190313/20111566WuCWO5Dora.png

select *,
MAX(CASE WHEN 1 = time_tag1 THEN on_time ELSE null END) OVER (PARTITION BY pers_name) 時間1
from(
select *,
row_number() over (PARTITION BY pers_name order by on_time) time_tag1
FROM "ithelp_20190312_t0"
) a

這整串SQL的概念是,先把一個人on_time的順序排出來(time_tag1),
接著你要建欄位嘛,那就先選第1個時間(WHEN 1 = time_tag1),
把這時間列出來,其他都是null,
再針對這個人max你列出來的第1時間和null,做over partition,
時間1就會出來了。

做出上面這張表之後,應該就會了吧?外面再包一層,
select和group by不要選到on_time和time_tag,
就會秀成你想要的那張表。
https://ithelp.ithome.com.tw/upload/images/20190313/20111566d3ogzxdq75.png
但如果你想往下挑戰function,
那這步因為是最後一步,先不急著做。
(如果往下的function不會,至少上面先學起來。)



2.萬一打卡資料超過四筆,要如何橫向將所有時間都顯示出來?

這問題麻煩的地方在於,你的欄位是動態的,
除了case when硬寫、特定SQL專屬的pivot,
其實最好的方式就是建function。

步驟一:建fun1,找到你到底需要多建幾個欄位出來?
從你給的資料來看,必須再往後多建4個欄位。
function跑出來確實也是【4】。
(SQL看不懂再留言問,這邊就先不解釋了。)
https://ithelp.ithome.com.tw/upload/images/20190313/201115667ngfcEdLsL.png

步驟二:建fun2,利用迴圈,把4個欄位建出來。
方式很多種,我用比較偷懶的方式,
每建一個欄位就建一張table,
然後再拿建出來的table再建一個欄位,
等於是會多建4個table就對了。
把fun1丟進fun2裡,fun2就會知道要跑4次。
https://ithelp.ithome.com.tw/upload/images/20190313/20111566JuB55mU9jf.png

fun2跑完,
會建出的最後一張table是ithelp_20190312_t4,
這也是我們要拿來用的。
https://ithelp.ithome.com.tw/upload/images/20190313/20111566msqWRf1yzm.png

步驟三:最前面講的最後一步,再select和group by一下,答案就出來了。
https://ithelp.ithome.com.tw/upload/images/20190313/20111566A6WIbCBLy5.png

當然啦~步驟三我有點偷懶沒繼續寫完,
想說直接看fun1結果,就知道要select和group by到【時間X】多少,
也會知道要select ithelp_20190312_tX,t4這個4是fun1跑出來的。

要繼續把X補全當然也是可以,
只是到此人工的部分已經降到很低了,就先這樣囉~
為了避免篇幅過大,詳細SQL如這裡呈現。

看更多先前的回應...收起先前的回應...
code iT邦新手 5 級 ‧ 2019-03-13 09:54:24 檢舉

謝謝,我會再研究研究

code iT邦新手 5 級 ‧ 2019-03-14 13:25:20 檢舉

大大 我已經可以做出如圖的表格了,但是又遇到另外一個問題,目前抓的資料都是當天有打卡的人員,那當天沒打卡但是請假的人員,有辦法一起列出來嗎??

這樣你需要先做出另外一份擁有完整員工名單的table:

select b.pers_cod,b.pers_nam,b.pers_ser
from pers_mn b where b.pers_cod='A001'
group by b.pers_cod,b.pers_nam,b.pers_ser

然後把上面這個當底、當基礎,LEFT JOIN你原本做出來的表格,類似如下:

select
t1.*,
t2.attnd_dat,
t2.時間1,
t2.時間2,
t2.時間3,
t2.時間4
from(
select b.pers_cod,b.pers_nam,b.pers_ser
from pers_mn b where b.pers_cod='A001'
group by b.pers_cod,b.pers_nam,b.pers_ser
)t1
LEFT JOIN (你做出的那串,記得要有pers_ser) t2
on t1.pers_ser=t2.pers_ser

這樣,有打卡的時間1~時間4能帶出來,沒打卡的也會直接是null。

你原本題目的下法,沒有寫join,直接是where a.pers_ser=b.pers_ser,這概念上是inner join,等於是必須a,b表都出現的資料,才會呈現出來。當現在你需要例如a沒有、b有的資料時,勢必得用 left join 或 right join。

code iT邦新手 5 級 ‧ 2019-03-18 17:43:05 檢舉

哈哈 大大我已經解決了 這幾天沒上來 還是感謝你唷

0
尼克
iT邦高手 1 級 ‧ 2019-03-12 15:47:31
code iT邦新手 5 級 ‧ 2019-03-12 15:49:47 檢舉

好的 會再研究看看 感謝感謝

我要發表回答

立即登入回答