各位大大好,最近在研究考勤系統,但是小弟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
資料截圖
希望能排列成如下
有以下兩個問題請教各位大大
1.SQL語法該怎麼下才能顯示出像圖二這樣的結果
2.萬一打卡資料超過四筆,要如何橫向將所有時間都顯示出來?
再麻煩各位大大了,跪~~
歡迎加入SQL function的世界
1.SQL語法該怎麼下才能顯示出像圖二這樣的結果
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,
就會秀成你想要的那張表。
但如果你想往下挑戰function,
那這步因為是最後一步,先不急著做。
(如果往下的function不會,至少上面先學起來。)
2.萬一打卡資料超過四筆,要如何橫向將所有時間都顯示出來?
這問題麻煩的地方在於,你的欄位是動態的,
除了case when硬寫、特定SQL專屬的pivot,
其實最好的方式就是建function。
步驟一:建fun1,找到你到底需要多建幾個欄位出來?
從你給的資料來看,必須再往後多建4個欄位。
function跑出來確實也是【4】。
(SQL看不懂再留言問,這邊就先不解釋了。)
步驟二:建fun2,利用迴圈,把4個欄位建出來。
方式很多種,我用比較偷懶的方式,
每建一個欄位就建一張table,
然後再拿建出來的table再建一個欄位,
等於是會多建4個table就對了。
把fun1丟進fun2裡,fun2就會知道要跑4次。
fun2跑完,
會建出的最後一張table是ithelp_20190312_t4,
這也是我們要拿來用的。
步驟三:最前面講的最後一步,再select和group by一下,答案就出來了。
當然啦~步驟三我有點偷懶沒繼續寫完,
想說直接看fun1結果,就知道要select和group by到【時間X】多少,
也會知道要select ithelp_20190312_tX,t4這個4是fun1跑出來的。
要繼續把X補全當然也是可以,
只是到此人工的部分已經降到很低了,就先這樣囉~
為了避免篇幅過大,詳細SQL如這裡呈現。
這樣你需要先做出另外一份擁有完整員工名單的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。
哈哈 大大我已經解決了 這幾天沒上來 還是感謝你唷