我需要帮助为 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          | 
+------------+-------------------+------------------+

我需要根据students area of​​ study找出一个subject's area of​​ study。从上面的学生表中可以看出，学生可以自由选择学习领域。有时，他们可以选择与他们的研究领域无关的科目。因此，在这种情况下，我们需要根据选修该科目的学生百分比计算科目的研究领域。

部分输出:

+------------+-------------------+-----------------------------------+
|Total_students |  subject_id    |area_of_study_id | percentage      |
+------------+-------------------+-----------------+-----------------+
| 3          | 1                 | 1               | 75              |
| 1          | 1                 | 2               | 25              |
| 1          | 3                 | 1               | 50              |
| 1          | 3                 | 2               | 50              |
| 1          | 2                 | 1               | 100             |
| 1          | 6                 | 3               | 100             |
+------------+-------------------+-----------------+-----------------+

从上面的部分输出中，我们可以看到，3 名学生选择了一个科目(即 subject_id =1)，他们的学习领域为 1，而一名学生来自不同的学习领域(即 2)。因此，我们可以说 subject_id =1 来自 area of​​ study = 1。

如果某个主题的百分比为 50%。我们可以在两者之间选择任何研究领域，无论是哪一个。

预期输出:

+------------+-------------------+-----------------------------------+
|Total_students |  subject_id    |area_of_study_id | percentage      |
+------------+-------------------+-----------------+-----------------+
| 3          | 1                 | 1               | 75              |
| 1          | 3                 | 1               | 50              |
| 1          | 2                 | 1               | 100             |
| 1          | 6                 | 3               | 100             |
+------------+-------------------+-----------------+-----------------+

最佳答案

这样试试

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 |

这是 SQLFiddle 演示

关于mysql - 计算研究领域的百分比，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/20183892/