如何计算研究区域的百分比？
如何计算研究区域的百分比？
提问于 2018-06-01 09:53:39
我需要帮助为MySQL数据库生成一个SQL。
我有两个表：
- 学生们
- student_Subjects
学生：
+------------+---------+-----------------+
| id | name | area_of_study_id |
+------------+---------+-----------------+
| 1 | AAA | 1 |
| 2 | BBB | 2 |
| 3 | CCC | 1 |
| 4 | DDD | 3 |
| 5 | EEE | 4 |
| 6 | FFF | 1 |
| 7 | GGG | 2 |
| 8 | III | 1 |
+------------+---------+-----------------+
student_subjects：
+------------+-------------------+------------------+
| id | student_id | subject_id |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 1 | 3 |
| 4 | 1 | 2 |
| 5 | 3 | 1 |
| 6 | 6 | 1 |
| 7 | 7 | 3 |
| 8 | 4 | 6 |
+------------+-------------------+------------------+
部分输出：
+------------+-------------------+-----------------------------------+
| Total_students | subject_id | area_of_study_id | 百分比|
+------------+-------------------+-----------------+-----------------+
| 3 | 1 | 1 | 75 |
| 1 | 1 | 2 | 25 |
| 1 | 3 | 1 | 50 |
| 1 | 3 | 2 | 50 |
| 1 | 2 | 1 | 100 |
| 1 | 6 | 3 | 100 |
+------------+-------------------+-----------------+-----------------+
预期：
+------------+-------------------+-----------------------------------+
| Total_students | subject_id | area_of_study_id | 百分比|
+------------+-------------------+-----------------+-----------------+
| 3 | 1 | 1 | 75 |
| 1 | 3 | 1 | 50 |
| 1 | 2 | 1 | 100 |
| 1 | 6 | 3 | 100 |
+------------+-------------------+-----------------+-----------------+
回答 1
Stack Overflow用户
发布于 2018-06-01 19:16:35
试试这种方式
SELECT subject_id, area_of_study_id, total_students, percentage
FROM
(
SELECT p.subject_id, p.area_of_study_id, p.total_students,
p.total_students / t.total_students * 100 percentage
FROM
(
SELECT COUNT(*) total_students, ss.subject_id, s.area_of_study_id
FROM students s JOIN student_subjects ss
ON s.id = ss.student_id
GROUP BY ss.subject_id, s.area_of_study_id
) p JOIN
(
SELECT ss.subject_id, COUNT(*) total_students
FROM students s JOIN student_subjects ss
ON s.id = ss.student_id
GROUP BY ss.subject_id
) t ON p.subject_id = t.subject_id
ORDER BY percentage DESC
) q
GROUP BY subject_id;
输出：
| SUBJECT_ID | AREA_OF_STUDY_ID | TOTAL_STUDENTS | PERCENTAGE |
|------------|------------------|----------------|------------|
| 1 | 1 | 3 | 75 |
| 2 | 1 | 1 | 100 |
| 3 | 1 | 1 | 50 |
| 6 | 3 | 1 | 100 |
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100008621
复制相关文章
相似问题