如何基于已查询的项目人员工时，计算个人工时占项目总工时的百分比

方案1：窗口函数实现（推荐，兼容PostgreSQL/MySQL 8.0+/SQL Server等所有现代SQL数据库） #

无需额外关联子查询，执行效率更高，代码更简洁：

SELECT 
    t."Resource" AS "Email",
    t."project id" AS "project_id",
    SUM(t."Hours Logged") AS "total hours",
    SUM(SUM(t."Hours Logged")) OVER (PARTITION BY t."project id") AS "overall total hours",
    ROUND(SUM(t."Hours Logged") * 1.0 / SUM(SUM(t."Hours Logged")) OVER (PARTITION BY t."project id") * 100, 2) AS "percentage"
FROM
  (SELECT DISTINCT "Resource",
                   "Hours Logged",
                   "proposals"."Clickup Id" AS "project id"
   FROM "daily hours",
        proposals AS "proposals"
   WHERE "When Logged" >= (CURRENT_DATE - 7)
     AND ("Space Id" = "proposals"."Clickup Id"
          OR "Folder Id" = "proposals"."Clickup Id"
          OR "List Id" = "proposals"."Clickup Id")) AS t
GROUP BY t."project id", t."Resource"

方案2：子查询关联实现（兼容不支持窗口函数的低版本数据库） #

适用于MySQL 5.7等老旧版本数据库：

WITH user_project_hours AS (
    SELECT DISTINCT t."Resource" AS "Email",
                    t."project id" AS "project_id",
                    sum(t."Hours Logged") AS "total hours"
    FROM
      (SELECT DISTINCT "Resource",
                       "Hours Logged",
                       "proposals"."Clickup Id" AS "project id"
       FROM "daily hours",
            proposals AS "proposals"
       WHERE "When Logged" >= (CURRENT_DATE - 7)
         AND ("Space Id" = "proposals"."Clickup Id"
              OR "Folder Id" = "proposals"."Clickup Id"
              OR "List Id" = "proposals"."Clickup Id")) AS t
    GROUP BY t."project id", t."Resource"
),
project_total AS (
    SELECT project_id, SUM("total hours") AS "overall total hours"
    FROM user_project_hours
    GROUP BY project_id
)
SELECT 
    u."Email",
    u."project_id",
    u."total hours",
    p."overall total hours",
    ROUND(u."total hours" * 1.0 / p."overall total hours" * 100, 2) AS "percentage"
FROM user_project_hours u
JOIN project_total p ON u.project_id = p.project_id

说明 #

• 两种方案都通过乘以1.0规避了部分数据库的整数除法问题，保证计算精度
• 使用ROUND()函数保留两位小数，和预期输出格式完全匹配
• 所有标识符的引用规则和原有SQL完全一致，无需调整现有表结构、字段名的写法

内容的提问来源于stack exchange，提问作者val ezeh