Oracle PL/SQL：去除跨大陆国家城市统计结果的重复行

现有city、country、Continent三张表，需要统计属于2个及以上大陆的国家的城市数量，输出格式为Country, Code, Continent1, Continent2, Count of Cities。已编写PL/SQL查询，但输出出现两行仅大陆顺序相反的重复结果（如俄罗斯的Asia-Europe与Europe-Asia行），需要去除其中一行。现有查询代码如下：

SELECT contCtry.nm   AS Country,
       contCtry.ctry AS Code,
       contCtry.co1  "Con1",
       contCtry.co2  "Con 2",
       contCtry.ctr  AS "Count of Cities"
FROM   (SELECT c1.NAME        nm,
               c.country      ctry,
               e.continent    co1,
               cont.continent co2,
               Count(c.NAME)  ctr
        FROM   city c
               JOIN continent e
                 ON c.country = e.country
                    AND e.country IN (SELECT ct.code
                                      FROM   country ct
                                             JOIN continent ec
                                               ON ct.code = ec.country
                                      GROUP  BY ct.code
                                      HAVING Count(ct.code) > 1)
               JOIN country c1
                 ON c1.code = e.country
               JOIN continent cont
                 ON cont.country = e.country
        WHERE  cont.continent != e.continent
        GROUP  BY c1.NAME,
                  c.country,
                  e.continent,
                  cont.continent) contCtry
ORDER  BY contCtry.co1,
          contCtry.co2,
          contCtry.nm; 

当前输出包含两行重复数据，仅大陆列顺序不同，寻求去除重复行的方法。

核心思路是让大陆名称按照固定顺序排列，避免生成双向的组合，以下两种方式都可以解决问题：

方式一：筛选大陆顺序减少冗余关联 #

修改子查询的WHERE条件，将cont.continent != e.continent替换为cont.continent > e.continent（或<，只要固定一个方向即可），这样只会保留大陆名称字母顺序符合要求的组合，不会生成反向重复行。

修改后的完整代码：

SELECT contCtry.nm   AS Country,
       contCtry.ctry AS Code,
       contCtry.co1  "Con1",
       contCtry.co2  "Con 2",
       contCtry.ctr  AS "Count of Cities"
FROM   (SELECT c1.NAME        nm,
               c.country      ctry,
               e.continent    co1,
               cont.continent co2,
               Count(c.NAME)  ctr
        FROM   city c
               JOIN continent e
                 ON c.country = e.country
                    AND e.country IN (SELECT ct.code
                                      FROM   country ct
                                             JOIN continent ec
                                               ON ct.code = ec.country
                                      GROUP  BY ct.code
                                      HAVING Count(ct.code) > 1)
               JOIN country c1
                 ON c1.code = e.country
               JOIN continent cont
                 ON cont.country = e.country
        -- 关键修改：固定大陆顺序，只保留单方向组合
        WHERE  cont.continent > e.continent
        GROUP  BY c1.NAME,
                  c.country,
                  e.continent,
                  cont.continent) contCtry
ORDER  BY contCtry.co1,
          contCtry.co2,
          contCtry.nm; 

方式二：用函数统一大陆列顺序 #

如果不想调整关联条件，可以使用LEAST()和GREATEST()函数，将两个大陆名称按字母顺序固定分配到co1和co2列，再进行分组统计，确保相同大陆组合只会出现一次。

修改后的代码：

SELECT contCtry.nm   AS Country,
       contCtry.ctry AS Code,
       contCtry.co1  "Con1",
       contCtry.co2  "Con 2",
       contCtry.ctr  AS "Count of Cities"
FROM   (SELECT c1.NAME        nm,
               c.country      ctry,
               -- 用LEAST取字母顺序靠前的大陆，GREATEST取靠后的，固定列顺序
               LEAST(e.continent, cont.continent) co1,
               GREATEST(e.continent, cont.continent) co2,
               Count(c.NAME)  ctr
        FROM   city c
               JOIN continent e
                 ON c.country = e.country
                    AND e.country IN (SELECT ct.code
                                      FROM   country ct
                                             JOIN continent ec
                                               ON ct.code = ec.country
                                      GROUP  BY ct.code
                                      HAVING Count(ct.code) > 1)
               JOIN country c1
                 ON c1.code = e.country
               JOIN continent cont
                 ON cont.country = e.country
        WHERE  cont.continent != e.continent
        GROUP  BY c1.NAME,
                  c.country,
                  LEAST(e.continent, cont.continent),
                  GREATEST(e.continent, cont.continent)) contCtry
ORDER  BY contCtry.co1,
          contCtry.co2,
          contCtry.nm; 

补充说明 #

• 方式一通过提前过滤冗余关联数据，性能更优；
• 方式二更灵活，适合需要保留原始关联逻辑的场景。

内容的提问来源于stack exchange，提问作者KudmiSubba