分享程序员开发的那些事...
更新时间：2023-01-21 11:09:05
此问题是查找孤立的子图".
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:
创建登台表:
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.