使用sql在多对多结构中查找关系 [英] find relationships in a many-to-many structure with sql

问题描述

我的问题接近亲子问题，可能需要一些递归查询，但是我没有通过浏览论坛找到任何答案. 这是我的问题:我有3张桌子:

my question is near a parent-child problem, and may need some recursive query, but i didn't find any answers by browsing forums. here is my problem: I've got 3 tables:

T1 (people)      T2 (places)  T3 (relationship betwenn A and B)
-------         ------      --------
id1 (pk)        id2 (pk)    id3 (pk)
name            city        id_A
                            id_B 

我想确定相关的地点和人群. 例如，如果
约翰访问伦敦和巴黎，
玛丽访问巴黎和纽约，
彼得访问班加罗尔和东京，
我想将同一组代码影响到Mary，John，巴黎，伦敦和纽约，并将另一组代码影响到Peter，东京和班加罗尔.

I would like to identify groups of places and people that are related. For example, if
John visits London and Paris,
Mary visits Paris and New York,
Peter visits Bangalore and Tokyo,
I would like to affect the same group code to Mary, John, Paris, London and New York, and another group code to Peter, Tokyo and Bangalore.

我真的不知道如何使用sql.有想法吗?

I really don't know how to do this with sql. Any Idea?

谢谢

推荐答案

此问题是查找孤立的子图".

This problem is "finding isolated subgraphs".

这是一个非常简单的问题，尽管无法通过单个SQL查询有效地解决.

It's quite a simple problem, though it cannot be efficiently solved with a single SQL query.

编写一个简单的存储过程很容易:

It's quite easy to write a simple stored procedure:

1. 创建登台表:

1. Create a staging table:

group_id   city_id

对于每个城市，找到其所有邻居的group_id(该城市的访客也曾去过的所有其他城市)

For each city, find group_id's of all its neighbors (all other cities the city's visitors have also visited)

如果邻居属于不同的group_id，则更新登台表，将所有group_id设置为集合中的最小集合.

If the neighbors belong to different group_id's, update the staging table, setting all the group_id's to the least one of the set.

将带有新group_id的城市插入到临时表中.

Insert the city with the new group_id into the staging table.

这篇关于使用sql在多对多结构中查找关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！