获取返回行值的百分比

e5nszbig  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(272)

希望获得每个“任务类型”的百分比值。看了很多例子,但不太明白。我尝试将下面的语句放入另一个select语句中以尝试分割,但它总是有sql错误。有什么想法吗?
以下是sql:

SELECT t.task_type, (SUM(trktm.time_end) - SUM(trktm.time_start)) as thetime
   FROM
   task as t, track_time as trktm
   WHERE
   trktm.time_start BETWEEN 1593932400 AND 1594450800
   AND
   trktm.time_end BETWEEN 1593932400 AND 1594450800
   AND
   t.user_id = 10
   GROUP BY t.task_type
   ORDER BY FIELD(t.task_type, 'Unassigned') DESC, t.task_type ASC

输出如下:

task_type   |  thetime
- - - - - - - - - - - - - -         
Unassigned  |  1041078
Bug Fixes   |  694052
Design      |  347026

但我要达到的目标是:

task_type   |  thetime   |   thepercent
- - - - - - - - - - - - - - - - - - - - -         
Unassigned  |  1041078   |   70.4
Bug Fixes   |  694052    |   19.6
Design      |  347026    |   10.0
b0zn9rqh

b0zn9rqh1#

直截了当的解决办法:加入totaltime

SELECT taskt_type, thetime, (thetime/totaltime) * 100 as thepercent From 
  (SELECT t.task_type, (SUM(trktm.time_end) - SUM(trktm.time_start)) as thetime
     FROM
       task as t, track_time as trktm
     WHERE
       trktm.time_start BETWEEN 1593932400 AND 1594450800
     AND
       trktm.time_end BETWEEN 1593932400 AND 1594450800
     AND
      t.user_id = 10
   GROUP BY t.task_type) A
JOIN 
  (SELECT  (SUM(trktm.time_end) - SUM(trktm.time_start)) as totaltime
     FROM
       task as t, track_time as trktm
     WHERE
       trktm.time_start BETWEEN 1593932400 AND 1594450800
     AND
       trktm.time_end BETWEEN 1593932400 AND 1594450800
     AND
      t.user_id = 10
   ) B
ORDER BY FIELD(task_type, 'Unassigned') DESC, task_type ASC

相关问题