php - 列出百分比

Question

我正在尝试计算测试答案的百分比。

我有 3 个表的 mysql 数据库

表测试

id | name
---+--------
1  | test 1
2  | test 2

表格问题以及测试问题

id | name       | test_id
---+------------+--------
1  | question 1 | 1
2  | question 2 | 1
3  | question 3 | 1
4  | question 4 | 1
5  | question 1 | 2
6  | question 2 | 2
7  | question 3 | 2
8  | question 4 | 2

表答案，我将测试中的所有答案放在其中

id | question_id
---+-------------
1  | 1
2  | 2
3  | 5
4  | 6
5  | 7
6  | 8

如何获取测试中已完成问题百分比的列表。在这种情况下是这样的:

name   | percentage
-------+-----------
test 1 | 50
test 2 | 100

Answer 1

尝试

select t.name, 
       cast((count(a.id) / count(q.id)) * 100 as unsigned) as percentage
from tests t
left outer  join questions q on q.test_id = t.id
left outer  join answers a on a.question_id = q.id
group by t.name

查看此SQLFiddle示例