检测MySQL数据库记录之间的关系 [英] Detecting relationships among MySQL DB records

问题描述

假设我有3个数据库表:

Let's say I have 3 DB tables:

1. 教师，具有以下字段:
   • id
   • 名称

1. teachers, with the following fields:
   • id
   • name

• id
• 名称

• teacher_id
• subject_id

因此，有一段恋爱关系会导致一些老师的科目相同.

So, a relationship comes some teachers have subjects in common.

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

I've thought about the algorithm for querying:

对于主题表中的每条记录:

For each record s in subjects table:

在教师主题表中选择计数.
如果计数大于1
在教师主题中，获取教师ID为s的教师ID.

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.

建立关系对.

我想获得类似以下内容的输出:

I'd like to get as output something like:

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

如果我需要检索与哪些人建立了联系，我们需要得到类似的东西:

And if I needed to retrieve which subjects made a relationship, we'd need to get something like:

+----+----------+----------+
| 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  |
+-----+------------+

以图形方式，这是通过Web浏览器查询的图形中表示的:

Graphically, I'm representing this in a graph consulted over a web browser:

因此，当我将一条鼠标悬停在一条边上时，它将显示关系信息.

So when I hover one edge, it displays the relationships information.

我将通过php编程接收查询结果，什么样的查询或存储过程会为我提供所需的输出?

I'll receive queries result over php programming, What such queries or stored procedure would produce me such required outputs?

推荐答案

这些是我对SQL的主张.

These are my propositions for SQL.

请记住，所有这些解决方案仅是对先前解决方案的一点修改.

Please remember, that every of these solutions is just a little modification from the previous ones.

第一张桌子

http://sqlfiddle.com/#!2/dc3f2/3

SELECT t1.name as 'Teacher1', t2.name as 'Teacher2', count(*) AS 'Relationships' 
FROM teachers t1 
JOIN teachers t2 ON t1.id > t2.id
JOIN teachers_subjects ts1 ON ts1.teacher_id = t1.id
JOIN teachers_subjects ts2 ON ts2.teacher_id = t2.id AND ts1.subject_id = ts2.subject_id
GROUP BY ts1.teacher_id, ts2.teacher_id;

第二张表

我认为它现在正在工作.

I think it's working now.

http://sqlfiddle.com/#!2/dc3f2/22

SELECT @rownum := @rownum + 1 as id, relations.Teacher1, relations.Teacher2
FROM (SELECT t1.name as 'Teacher1', t2.name as 'Teacher2'
FROM teachers t1 
JOIN teachers t2 ON t1.id > t2.id
JOIN teachers_subjects ts1 ON ts1.teacher_id = t1.id
JOIN teachers_subjects ts2 ON ts2.teacher_id = t2.id AND ts1.subject_id = ts2.subject_id
GROUP BY ts1.teacher_id, ts2.teacher_id) as relations,
(SELECT @rownum := 0) r;

第三张表

http://sqlfiddle.com/#!2/dc3f2/27

SELECT id, subject
FROM
(SELECT @rownum := @rownum + 1 as id, relations.Teacher1, relations.Teacher2, relations.id1, relations.id2
FROM (SELECT t1.name as 'Teacher1', t2.name as 'Teacher2', t1.id as 'id1', t2.id as 'id2'
FROM teachers t1 
JOIN teachers t2 ON t1.id > t2.id
JOIN teachers_subjects ts1 ON ts1.teacher_id = t1.id
JOIN teachers_subjects ts2 ON ts2.teacher_id = t2.id AND ts1.subject_id = ts2.subject_id
GROUP BY ts1.teacher_id, ts2.teacher_id) as relations,
(SELECT @rownum := 0) r) as rel
JOIN
(SELECT t1.id as 'id1', t2.id as 'id2', s.name as 'subject'
FROM teachers t1 
JOIN teachers t2 ON t1.id > t2.id
JOIN teachers_subjects ts1 ON ts1.teacher_id = t1.id
JOIN teachers_subjects ts2 ON ts2.teacher_id = t2.id AND ts1.subject_id = ts2.subject_id
JOIN subjects s ON ts1.subject_id = s.id) as rel_subjects
ON rel.id1 = rel_subjects.id1 AND rel.id2 = rel_subjects.id2
ORDER BY id

关于最后一件事.请记住，用于关系的那些伪密钥是非常可变的.这意味着，如果添加新行并删除一些行，则将更改这些关系的ID.因此，为了避免出现任何错误，您不能在数据库中的任何存储列中使用它们，而只能在SELECT查询中使用它们.

On last thing. Please remember, that those pseudo keys for relationships are very changeable. That means that if you add new rows and remove some you will change those relationship's IDs. So not to get any errors you cannot use them in any storage column wherever in your database - just use them in SELECT queries.

这篇关于检测MySQL数据库记录之间的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！