php - 检测 MySQL 数据库记录之间的关系

Question

假设我有 3 个数据库表:

1. 教师，具有以下领域:
   • 编号
   • 姓名
2. 主题，包含以下字段:
   • 编号
   • 姓名
3. teachers-subjects，具有以下字段:
   • teacher_id
   • 主题编号

所以，一些老师有共同的科目。

我考虑过用于查询的算法:

For each record s in subjects table:

Select the count in teachers_subjects table.
If count is greater than 1
In teacher_subjects, get teacher_id's where subject_id is s.

建立关系对。

我想得到这样的输出:

+----------+----------+---------------+
| Teacher1 | Teacher2 | Relationships |
+----------+----------+---------------+
| John     | Larry    |            10 |
| John     | Samantha |            12 |
| Samantha | Larry    |             9 |
| Ian      | Louis    |             3 |
+----------+----------+---------------+

如果我需要检索哪些对象建立了关系，我们需要得到如下内容:

+----+----------+----------+
| id | teacher1 | teacher2 |
+----+----------+----------+
|  1 | John     | Larry    |
|  2 | John     | Samantha |
|  3 | Samantha | Larry    |
|  4 | Ian      | Louis    |
+----+----------+----------+

还有类似的东西:

+-----+------------+
| id  |  subject   |
+-----+------------+
| 1   | math       |
| 1   | english    |
| 1   | science    |
| ... | ...        |
| ... | ...        |
| 4   | science    |
| 4   | literature |
| 4   | databases  |
+-----+------------+

在图形上，我在通过网络浏览器咨询的图表中表示:

所以当我将鼠标悬停在一个边缘时，它会显示关系信息。

我将通过 php 编程接收查询结果，什么样的查询或存储过程会产生我需要的输出？

Answer 1

试试这个(你应该在 SQLFiddle 中加载一些示例数据以便更好地测试)

SELECT t1.`name` AS teacher1, t2.`name` AS teacher2, count(*)
FROM teachers AS t1
JOIN teachers AS t2
  ON t1.id > t2.id
JOIN teacher_subjects AS ts1
  ON ts1.teacher_id = t1.id
JOIN teacher_subjects AS ts2
  ON ts2.teacher_id = t2.id
    AND ts2.subject_id = ts1.subject_id
GROUP BY teacher1, teacher2
ORDER BY COUNT(*) DESC;