mysql - 计算多列值的百分比

Question

我想找到 MySql 查询来计算基于不同列上的值完成的工作的百分比，以前我在 Microsoft Excel 中使用 countif 执行此操作，但现在我想在 mysql 上创建相同的表。

这是我当前的表格

Parts Already Build : 
------------------------------------------------------------------
| id | Front   |  Rear   |  Left   | Right   |  Up     |  Down   |
__________________________________________________________________

| 01 | Done    |  Done   | Done    | Done    | Done    |  Done   |
------------------------------------------------------------------
| 02 | Done    |  Done   | Done    | Done    | No Need | No Need |
------------------------------------------------------------------
| 03 | Done    | Ongoing | Ongoing | Done    | Done    | Not Yet | 
------------------------------------------------------------------
| 04 | Not Yet | Not Yet | Not Yet | Not Yet | Not Yet | Not Yet |
------------------------------------------------------------------

预期结果

Parts Already Build : 
--------------------------------------------------------------------------------
| id | Front   |  Rear   |  Left   | Right   |  Up     |  Down   |   %   |  Status  |
________________________________________________________________________________

| 01 | Done    |  Done   | Done    | Done    | Done    |  Done   |  100  | Complete |
--------------------------------------------------------------------------------
| 02 | Done    |  Done   | Done    | Done    | No Need | No Need |  100  | Complete |
--------------------------------------------------------------------------------
| 03 | Done    | Ongoing | Ongoing | Done    | Done    | Not Yet |   50  | Ongoing  |
--------------------------------------------------------------------------------
| 04 | Not Yet | Not Yet | Not Yet | Not Yet | Not Yet | Not Yet |    0  | Not Yet  |
--------------------------------------------------------------------------------

注意:

1. 如果所有流程均已完成，或者第 6 列中有 6 个“完成”值，则 % 列 = 100
2. 如果值为“不需要”，则视为“完成”。
3. 如果并非所有流程都已完成/没有流程完成，则表格将仅计算有多少“已完成”或“不需要”值，并将其转换为 % 列中的百分比(3/6 流程已“完成”= 50%)
4. 如果 % =100%，则“状态”列 = 完成/如果 =0% = 尚未/如果其他(1%-99% = 正在进行)

当前查询:

SELECT
front,
rear,
left,
right,
up,
down,
_________________ AS %,
_________________ AS Status
FROM process

Answer 1

对于给定的表结构，您需要将所有状态为 Done 或 No Need 的情况相加，以获得完整性值，然后就可以用于生成 % 和 status 列:

SELECT `Front`, `Rear`, `Left`, `Right`, `Up`, `Down`,
        complete * 100 / 6 AS percent,
        CASE complete WHEN 6 THEN 'Complete'
                      WHEN 0 THEN 'Not Yet'
                      ELSE 'Ongoing'
        END AS status
FROM (SELECT *,
             0 + (Front = 'Done' OR Front = 'No Need')
               + (Rear  = 'Done' OR Rear = 'No Need')
               + (`Left`  = 'Done' OR `Left` = 'No Need')
               + (`Right` = 'Done' OR `Right` = 'No Need')
               + (Up    = 'Done' OR Up = 'No Need')
               + (Down  = 'Done' OR Down = 'No Need') AS complete
      FROM process) p

输出

Front       Rear        Left        Right       Up          Down        percent     status
Done        Done        Done        Done        Done        Done        100     Complete
Done        Done        Done        Done        No Need     No Need     100     Complete
Done        Ongoing     Ongoing     Done        Done        Not Yet     50      Ongoing
Not Yet     Not Yet     Not Yet     Not Yet     Not Yet     Not Yet     0       Not Yet

Demo on dbfiddle