我正在使用WinSQL精简版，并尝试使用DATEPART，但不确定如何使用

Question

我需要按小时对这个SQL字符串进行分组，以表示一个USER_ID，但是我不确定如何使用我一直在阅读的DATEPART函数。基本上，我希望获得该用户从开始工作到回家的每一个小时的性能数据。如果用户早上6点来，我想看看他们在7 am,8am,9am...etc时的性能数据是什么，这样我就可以将数据导入excel并制作图表。

-------EmpPerf--------

select u.user_id, SUM(tl.elapsed_time) AS ELAPSED, SUM(CASE WHEN 
(tl.standard_time = 0) THEN 0 ELSE (tl.elapsed_time) END)AS PERFTIME, 
SUM(tl.standard_time)as Standard_time

from perfplus.tale tl, perfplus.task tk, perfplus.users u

WHERE tl.task_id = tk.ID
AND tl.facility_id = tk.facility_id
AND tk.start_time BETWEEN {ts '2017-05-04 04:00:00'} and {ts '2017-05-05 03:59:59'}
AND tk.facility_id = '130'
AND tk.user_id = u.user_id
AND u.shift_name NOT IN ('9WMS','9','T2016','T2017')
and tk.status like ('TS_PROCESSED')
and u.user_id = 'LBRSHALL'

group by u.user_id

Answer 1

弄清楚了：

-------EmpPerf--------

select hour(tk.start_time), SUM(tl.elapsed_time) AS ELAPSED, SUM(CASE WHEN 
(tl.standard_time = 0) THEN 0 ELSE (tl.elapsed_time) END)AS PERFTIME, 
SUM(tl.standard_time)as Standard_time
from perfplus.tale tl, perfplus.task tk, perfplus.users u
WHERE tl.task_id = tk.ID
AND tl.facility_id = tk.facility_id
AND tk.start_time BETWEEN {ts '2017-05-04 04:00:00'} and {ts '2017-05-04 
23:00:00'}

AND tk.facility_id = '130'
AND tk.user_id = u.user_id
AND u.shift_name NOT IN ('9WMS','9','T2016','T2017')
and tk.status like ('TS_PROCESSED')
and u.user_id = 'LBRSHALL'

group by hour(tk.start_time)
order by hour(tk.start_time)