我在表 AttendanceReg 中有員工的移動資料，例如：
| EmpId | 到位 | 銀泰 | 時差 |
|---|---|---|---|
| 001 | PLC01 | 上午 10 點 | 上午11點 |
| 002 | PLC01 | 上午 10 點 | 上午11點 |
| 001 | plc02 | 上午11點 | 中午 12 點 |
| 002 | plc02 | 上午11點 | 07PM |
| 001 | PLC01 | 中午 12 點 | 06PM |
| 003 | PLC01 | 上午 10 點 | 08PM |
我想要一份關于員工第一進后出的報告。（應該取決于進出時間，而不是地點）
當我嘗試聚合函式和分組依據時，它回傳：
Alter PROCEDURE UDP_AttReport @SelectDt Date
AS
select EmpID as ID,max(InPlace) as InPlace,min(InTime) as InTime Into #AttIN from AttendanceReg where cast(InTime as Date) = cast(@SelectDt as date) group by EmpID order by InTime desc
select EmpID as ID, max(InPlace)as OutPlace,max(OutTime) as OutTime Into #AttOut from AttendanceReg where cast(InTime as Date) = cast(@SelectDt as date) group by EmpID order by OutTime desc
SELECT #AttIN.ID,InPlace,InTime,OutPlace,OutTime Into #AttRep FROM #AttIN
RIGHT JOIN #AttOut
ON #AttIN.ID = #AttOut.ID
select ID,InPlace,InTime,OutPlace,OutTime from #AttRep Order by InTime asc
drop table #AttIN
drop table #AttOut
drop table #AttRep
Go
| EmpId | 到位 | 銀泰 | 外地 | 時差 |
|---|---|---|---|---|
| 001 | plc02 | 上午 10 點 | plc02 | 06PM |
| 002 | plc02 | 上午11點 | plc02 | 07PM |
| 003 | PLC01 | 上午 10 點 | PLC01 | 08AM |
但我期望的是
| EmpId | 到位 | 銀泰 | 外地 | 時差 |
|---|---|---|---|---|
| 001 | PLC01 | 上午 10 點 | PLC01 | 06PM |
| 002 | PLC01 | 上午 10 點 | plc02 | 07PM |
| 003 | PLC01 | 上午 10 點 | PLC01 | 08PM |
我可以理解問題在于使用 Max(InPlace) （我在某處學到了它來欺騙 SQL 以回傳該列而不按該列分組）。
謝謝。
uj5u.com熱心網友回復：
試試這個 ：
Select a.EmpId,
a.InPlace,
a.InTime,
b.Inplace as Outplace ,
b.OutTime
From
(Select EmpId, InPlace, InTime, ROW_NUMBER() over(partition by EmpId order by InTime) rn From test) a
inner join
(Select EmpId, InPlace, OutTime, ROW_NUMBER() over(partition by EmpId order by OutTime desc) rn From test) b
on
a.EmpId = b.EmpId
Where
a.rn = 1
and b.rn = 1
輸出
uj5u.com熱心網友回復：
使用視窗函式row_number()查找第一個和最后一個In或Out按EmpId
with cte as
(
select *,
rn = row_number() over (partition by EmpId order by InTime),
rc = count(*) over (partition by EmpId)
from AttendanceReg
)
select EmpId,
InPlace = max(case when rn = 1 then InPlace end),
InTime = max(case when rn = 1 then InTime end),
OutPlace = max(case when rn = rn then InPlace end),
OutTime = max(case when rn = rn then OutTime end)
from cte
group by EmpId
OutTimefor有錯誤003嗎？它應該是08PM？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/478962.html
上一篇：使用SQL從字串中提取ID