php - 我需要计算计数项目的百分比

Question

我有两个表:

第一:原因

id   |   title   
---------------------------------  
1    |   Customer didn't like it  
2    |   Needs improving         
3    |   Wrong format 

第二:项目

id   |   title       |   rejected
------------------------------------  
1    |   Priject 1   |   Null
2    |   Priject 2   |   1  
3    |   Priject 3   |   1  
4    |   Priject 4   |   Null  
5    |   Priject 5   |   2

我需要显示 Reasons.Title 以及因该原因被拒绝的项目数量。我已经设法用这段代码将这些表连接在一起

SELECT reasons.title as title, count(*) as num
FROM reasons
LEFT JOIN reasons on projects.rejected = reasons.id
WHERE projects.rejectedIS NOT NULL
GROUP BY projects.rejected

现在我需要添加百分比，所以我的决赛 table 看起来像这样

title                    |   num   |  percentage 
--------------------------------------------------  
Customer didn't like it  |   2     |  66,6
Needs improving          |   1     |  33,3

百分比的格式当然不重要。我想用 MySql 完成这项工作，所以我不需要使用两个查询和额外的 PHP，但如果有其他解决方案，除了 MySql，我愿意接受建议

Answer 1

您可以通过在 FROM 子句中获取总数来做到这一点:

SELECT r.title as title, count(*) as num,
       COUNT(*) / pp.cnt as ratio
FROM reasons r JOIN 
     projects p
     ON p.rejected = r.id CROSS JOIN
     (SELECT COUNT(*) as cnt FROM projects p WHERE rejects IS NOT NULL) pp
GROUP BY r.title, pp.cnt;

注意事项:

• 这修复了表名，因此查询有一个 projects 表。
• 这将删除 WHERE，因为不需要它。
• 这会将 LEFT JOIN 更改为内部联接。