WITH TaskDuration AS (
SELECT
t.TaskTypeID,
t.TaskName,
SUM(tr.Time) AS TotalTime
FROM
Tasks t
INNER JOIN
TaskRecords tr ON t.TaskID = tr.TaskID
WHERE
tr.UserName = '指定的用户名' -- 替换为您的用户名
AND tr.DateTime BETWEEN '2023-05-01' AND '2023-05-30'
GROUP BY
t.TaskTypeID,
t.TaskName
),
TotalDuration AS (
SELECT
SUM(TotalTime) AS GrandTotalTime
FROM
TaskDuration
)
SELECT
td.TaskTypeID,
tt.TaskTypeName,
td.TaskName,
td.TotalTime,
td.TotalTime * 100.0 / td2.GrandTotalTime AS Percentage
FROM
TaskDuration td
INNER JOIN
TaskTypes tt ON td.TaskTypeID = tt.TaskTypeID
CROSS JOIN
TotalDuration td2;
被迫营业的小白(提问者)大佬，这是修改过的sql，发现不输入员工姓名，查全部员工5月份数据的时候，每个人的所有任务耗时是全部人员所有任务耗时，这个怎么修改：
WITH TaskDuration AS
(SELECT tr.EmployeeName,t.TaskTypeId,t.TaskTitle,SUM(tr.TimeConsume) AS TotalTime FROM Tasks t
INNER JOIN TaskRecords tr ON t.TaskId = tr.TaskId WHERE 1=1 AND tr.EFFE_FLAG !=0 AND t.EFFE_FLAG!=0
${if(len(text_employee_name)==0,"","AND tr.EmployeeName like'"+'%'+text_employee_name+'%'+"'")}
${if(len(dateEditor_from)==0,"","and tr.DATA_TIME >='"+ dateEditor_from + "'")} ${if(len(dateEditor_to)==0,"","and tr.DATA_TIME <='"+dateEditor_to + "'")}
GROUP BY tr.EmployeeName,t.TaskTypeId,t.TaskTitle),TotalDuration AS (SELECT SUM(TotalTime) AS GrandTotalTime FROM TaskDuration )
SELECT
${if(and ((len(text_employee_name))==0,len(dateEditor_from)==0,len(dateEditor_to)==0),"top 100","")}
td.EmployeeName, tt.TaskTypeName,td.TaskTitle,td.TotalTime,(td.TotalTime / td2.GrandTotalTime)*100 AS Percentage,td2.GrandTotalTime
FROM TaskDuration td INNER JOIN TaskTypes tt ON td.TaskTypeId = tt.TaskTypeId CROSS JOIN TotalDuration td2;
1min目标场景问卷
立即参与
