查询所有被另一个国家隔开的国家对

Question

查询所有被另一个国家隔开的国家对

3

感谢您的时间。

我有来自mondial数据库（网站，文档）的以下表格。

CREATE TABLE borders
(Country1 VARCHAR(4),
 Country2 VARCHAR(4),
 Length FLOAT, 
 CONSTRAINT CHECK (Length > 0),
 CONSTRAINT BorderKey PRIMARY KEY (Country1,Country2));

表格中不包含互相的值（它只包含 Country1，Country2 或 Country2，Country1 来定义边界）。

我需要制作一个查询，输出所有不是邻国但是与邻国相邻的国家对（所有被另一个国家隔开的国家对）。

Country1 和 Country2 包含诸如“F”代表法国，“I”代表意大利等国家代码。以下是输出的示例行：

RSM || F

RSM 是圣马力诺共和国的代码（圣马力诺是完全被意大利包围的飞地）。显然，RSM 不是法国的邻国，但意大利是，因此输出包含了RSM，F 这对国家，还包含了所有其他与意大利接壤的国家的类似情况。

我已经花费了数小时尝试解决方案，但离解决方案还有很远的路要走，我已经有很多问题了，以下是我所做的：

我开始制作一个查询，找出某个国家的所有邻国。

SELECT Country1
FROM borders
WHERE Country2 = "RSM"
UNION
SELECT Country2
FROM borders
WHERE Country1 = "RSM"

这显然只输出"I"，这是意大利的代码（所以是正确的）。我已经有一个问题：是否有更好的方法来做这个或者这样做是可以的？

然后我接着向下一步走，查询找到所有邻居的邻居，如下：

SELECT Country1
FROM borders
WHERE Country2 IN (
    SELECT Country1
    FROM borders
    WHERE Country2 = "RSM"
    UNION
    SELECT Country2
    FROM borders
    WHERE Country1 = "RSM"
)
UNION
SELECT Country2
FROM borders
WHERE Country1 IN (
    SELECT Country1
    FROM borders
    WHERE Country2 = "RSM"
    UNION
    SELECT Country2
    FROM borders
    WHERE Country1 = "RSM"
)

所以，我基本上做的是相同的事情，但不是寻找特定国家的邻居，而是搜索某些国家的所有邻居。查询尚未完成，因为我需要从输出中删除起始国家及其所有邻居，因此查询变成了这样：

SELECT "RSM", Country1 
FROM (
    SELECT Country1
    FROM borders
    WHERE Country2 IN (
        SELECT Country1
        FROM borders
        WHERE Country2 = "RSM"
        UNION
        SELECT Country2
        FROM borders
        WHERE Country1 = "RSM"
    )
    UNION
    SELECT Country2
    FROM borders
    WHERE Country1 IN (
        SELECT Country1
        FROM borders
        WHERE Country2 = "RSM"
        UNION
        SELECT Country2
        FROM borders
        WHERE Country1 = "RSM"
    )
) tmp
WHERE tmp.Country1 NOT IN (
    SELECT Country1
    FROM borders
    WHERE Country2 = "RSM"
    UNION
    SELECT Country2
    FROM borders
    WHERE Country1 = "RSM"
) AND tmp.Country1 != "RSM"

这个查询适用于单个起始国家（RSM），但我需要输出所有不同的夫妻组合（意思是，如果输出中存在RSM，F，则没有F，RSM），如上所述，而且我认为我编写的查询非常糟糕，因为它一遍又一遍地重复相同的查询。我已经搜索了避免这种情况的方法，并找到了一些有趣的解决方案，例如WITH tmp AS query子句，但我正在使用MySQL，不支持WITH AS子句。

那么你们专家认为呢？这至少接近正确的方式吗？我错过了一些明显的东西吗？

提前感谢您的时间，如果这个可怕的查询给您带来了头痛，对不起。

编辑1：我制作了一个SQL Fiddle，其中包含表格、数据和我的查询，以便您可以轻松运行查询。我希望它能对某人有所帮助。

- xuT

添加一个约束条件，使得 country1 < country2，以便简化操作（例如，避免重复）。 - jarlh

坦白说，我不确定你的意思，但我不能编辑表格，我只能开发查询。 - xuT

1

@TheTux 我本来要回答的，但它和Gordon发表的答案太相似了，我不想冒险抄袭他的答案。无论如何，请看这个：样例 SQL Fiddle，显示所有与意大利（I）接壤的国家。 - jpw

1

@TheTux 是的，它对我有效，但是速度相当慢；我猜你可能已经超时了。这里有一个带有代码的pastebin链接：http://pastebin.com/4gQw2fZA - jpw

你的查询似乎完美无缺。我必须感谢你们，因为你们让我意识到了视图的强大之处，如果表格稍微不同，这个查询将会更加容易。现在只剩下一个问题：我必须从你的查询结果中删除所有重复的组合（例如，如果RSM || F存在，则不应存在F || RSM）。 - xuT

显示剩余4条评论

3个回答

1

用户jpw在评论中解决了问题。

许多用户建议制作一个表格视图，以使夫妻或国家之间对称，如下所示：

CREATE VIEW borders_symmetrical AS
SELECT Country1, Country2 FROM borders
UNION ALL
SELECT Country2, Country1 FROM borders;

在我看来，这确实是一个很好的建议，并且表明视图可以非常有用。现在查询变得更加容易了:

SELECT DISTINCT b1.Country1, b2.Country2 
FROM borders_symmetrical b2
JOIN borders_symmetrical b1
ON b2.Country1 = b1.Country2 
WHERE b2.Country2 <> b1.Country1
    #AND (b1.Country1 = 'RSM' OR b2.Country2 = 'RSM') # Debug a single nation
    AND b2.country2 NOT IN (
        SELECT Country2
        FROM borders_symmetrical 
        WHERE Country1 = b1.Country1
    ) AND b1.Country1 < b2.Country2

我重新发布了这个帖子，因为jpw只是在评论中回答而没有提交答案。

再次感谢大家宝贵的时间，你们帮助了我很多，让我理解了关于SQL和数据库的一些重要事情。

- xuT

0

这是我能想到的最短的方法，而不需要修改你所选择的数据库：

SELECT DISTINCT reciprocalBorders1.first, reciprocalBorders2.second, reciprocalBorders1.second
FROM   (SELECT Country1 first, Country2 second FROM borders UNION
        SELECT Country2 first, Country1 second FROM borders) reciprocalBorders1,
       (SELECT Country1 first, Country2 second FROM borders UNION
        SELECT Country2 first, Country1 second FROM borders) reciprocalBorders2
WHERE reciprocalBorders1.second = reciprocalBorders2.first
  AND reciprocalBorders1.first < reciprocalBorders2.first
  AND reciprocalBorders1.first <> reciprocalBorders2.second
  AND NOT EXISTS(
    SELECT 1
    FROM   (SELECT Country1 first, Country2 second FROM borders UNION
            SELECT Country2 first, Country1 second FROM borders) reciprocalBorders3
    WHERE   reciprocalBorders3.first = reciprocalBorders1.first
      AND   reciprocalBorders3.second = reciprocalBorders2.second
  )
ORDER BY 1, 2, 3;

第三列包含将第一列和第二列中的国家分开的国家。显然，可以通过使用视图来缩短FROM部分。每个FROM部分都将反转表添加到自身中，以便在一个表中具有“ A | F”和“ F | A”条目。

关于WHERE部分：

第一行连接了两个互补的表，以便像“ A | CH”和“ CH | F”这样的对被连接在一起。
第二行确保如果分析组合“ A | CH”，“ CH | F”，则不会分析“ F | CH”，“ CH | A”
第三行确保您不会收到类似“ A | CH”，“ CH | A”的结果
第四行确保您不会收到直接边界的结果。

SELECT DISTINCT确保您不会获得与两个或更多相同国家共享边界的A和B国家的双重结果，例如奥地利与瑞士和德国相连，两者都与法国共享边界，但您不希望有两个奥地利，法国的条目。

- Tim Meyer

在筛选时，您可以将例如 AND ( reciprocalBorders1.first = 'RSM' OR reciprocalBorders2.second = 'RSM' ) 添加到查询的 WHERE 部分。完全有可能我筛选过多了，我想我得研究一下这个。 - Tim Meyer

随意使用视图，如果需要，我将用视图的查询替换每个视图的使用。现在，我只想知道如何以最简单的方式解决这个查询。顺便说一下，我创建了一个 SQL Fiddle，让你看看为什么你的查询是错误的，http://sqlfiddle.com/#!9/0f9cc/31。正如你可以看到的，只有3行数据，而不是问题中展示的5行。 - xuT

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

假设数据具有互相的值，意味着数据中既有 ('France', 'Italy') 也有 ('Italy', 'France')，那么你可以通过连接和过滤来实现这一点：

select b1.country1, b2.country2
from borders b1 join
     borders b2
     on b1.country2 = b2.country1
where not exists (select 1
                  from borders b
                  where b.country1 = b1.country1 and b.country2 = b2.country2
                 );

编辑：如果没有互惠值，我会创建一个视图并使用该视图进行查询：

create view v_borders as
    select country1, country2 from borders union all
    select country2, country1 from borders;

然后在查询中使用该视图。此外，您也可以在查询中执行此操作，但这很麻烦，因为MySQL不支持公共表达式（CTE）。