php - MySQL查询以显示基于三个表的格式

Question

我在下面的三个表格中显示了学生记录、科目和带科目的学生。

请问什么是有效的SQL查询来显示下面的结果。我可以使用 JOIN 显示它，但不能使用下面的格式。

+------+-----------+-----------+-----+----------+-----------+--------+| Name | Address   | Telephone | Sex | Subjects | Teacher   | Active |+------+-----------+-----------+-----+----------+-----------+--------+| John | somewhere | 12345     | M   |          | Teacher 1 | YES    || John | somewhere | 12345     | M   | Math     |           | YES    || John | somewhere | 12345     | M   | Science  |           | YES    || John | somewhere | 12345     | M   | English  |           | YES    || Matt | somewhere | 123456    | M   |          | Teacher 2 | YES    || Matt | somewhere | 23456     | M   | Math     |           | YES    || Matt | somewhere | 123456    | M   | Science  |           | YES    || Girl | somewhere | 5431      | F   |          | Teacher3  | YES    || Girl | somewhere | 5431      | F   | Physics  |           | YES    || Girl | somewhere | 5431      | F   | Math     |           | YES    |+------+-----------+-----------+-----+----------+-----------+--------+select * from student_record;+------------+------+-----------------+-----------+-----+----------+--------+| id_student | name | address         | telephone | sex | teacher  | active |+------------+------+-----------------+-----------+-----+----------+--------+|          1 | John | Somewhere       | 12345     | M   | Teacher  | 0      ||          2 | Matt | Somewhere There | 12345222  | M   | Teacher1 | 0      ||          3 | Girl | Somewhere here  | 3333      | F   | Teacher2 | 0      |+------------+------+-----------------+-----------+-----+----------+--------+select * from subjects;+------------+--------------+---------------------+| id_subject | subject_name | subject_description |+------------+--------------+---------------------+|          1 | Math         | Math                ||          2 | Science      | Science             ||          3 | English      | English             ||          4 | Physics      | Physics             |+------------+--------------+---------------------+select * from with_subjects;+--------------------+--------------------+------------+| id_student_subject | student_id_subject | student_id |+--------------------+--------------------+------------+|                  1 |                  1 |          1 ||                  2 |                  2 |          1 ||                  3 |                  3 |          1 ||                  4 |                  4 |          1 ||                  5 |                  4 |          2 ||                  6 |                  3 |          2 ||                  8 |                  1 |          2 ||                  9 |                  1 |          3 ||                 10 |                  2 |          3 ||                 11 |                  3 |          3 ||                 12 |                  4 |          3 |+--------------------+--------------------+------------+

Answer 1

怎么样

选择a.name作为“姓名”，a.address作为“地址”，a.telephone作为“电话”，a.sex作为“性别”，null作为“主题”，a.teacher作为“教师”， a.active 作为 student_record 中的“Active”作为 aunion a.name为“姓名”，a.address为“地址”，a.telephone为“电话”，a.sex为“性别”，b.subject_name为“主题”，null为“老师”，a.active as "Active"from (student_record as a inner join with_subjects as c on a.id_student = c.student_id) inner join subjects as b on c.student_id_subject = b.id_subject

没有测试过。它的顺序与您的示例相同，但应该包含所有数据