如何在SQL中从两个列中获取唯一对的数据

Question

我卡在了来自http://www.sql-ex.ru/learn_exercises.php#answer_ref的第68个练习上。

该数据库由以下四个表组成：

1. 公司(ID_comp, name)
2. 旅行(trip_no, id_comp, plane, town_from, town_to, time_out, time_in)
3. 乘客(ID_psg, name)
4. 乘客信息(trip_no, date, ID_psg, place)

该练习的目标是： 找出有最多航班（旅行）的路线数量。 注释：

1. A - B和B - A被视为同一条路线。
2. 只使用Trip表。

我可以得到“找出有最多航班（旅行）的路线数量”的正确答案，但是当考虑注释1时，即A - B和B - A被视为同一条路线时，我不知道如何获取唯一的对：如果我们有以下输出：

| town_from | town_to   | count |
| --------- | --------- | ----- |
| London    | Singapore | 4     |
| Singapore | London    | 4     |

如何选择只给我“

”？

| town_from | town_to   | count |
| --------- | --------- | ----- |
| London    | Singapore | 4     |

我能够使用以下查询完成问题：

WITH x AS( SELECT con, sum(c) as s FROM( SELECT town_from, town_to, 'con' = CASE WHEN lower(town_from)

SELECT count(*) FROM x WHERE s = (SELECT max(s) FROM x)

（注：该段文本似乎存在一些缺失或错误，请提供更多上下文以便我更好地理解和翻译。）

- Uncle Jesse

---

你使用哪个数据库？MySQL还是SQL Server？请正确标记。 - Gordon Linoff

请发表尝试查询以得出您当前输出的内容。 - Parfait

Answer 1

你需要以与从->到相同的方式查看Trip表，就像它对于到->从一样。通常的做法是确保它始终按照town_from <(=) town_to排序。

一般来说，以下查询将以这种方式投影Trip。case语句切换from和to以使它们始终排序：

select trip_no, id_comp, plane, 
    case when town_from < town_to then town_from else town_to end as town_from, 
    case when town_from < town_to then town_to else town_from end as town_to, 
    time_out, time_in
from Trip

您可以在查询中从这个投影中选择:

select ...
from (
    select trip_no, id_comp, plane, 
        case when town_from < town_to then town_from else town_to end as town_from, 
        case when town_from < town_to then town_to else town_from end as town_to, 
        time_out, time_in
    from Trip
) as x

为了解决这个特定问题，我们可以应用相同的逻辑，但是删除不必要的列（优化器应该会自动处理，但对于人类来说看起来更清晰）。

select town_from, town_to, count(*)
from (
    select 
        case when town_from < town_to then town_from else town_to end as town_from, 
        case when town_from < town_to then town_to else town_from end as town_to 
    from Trip
) as x
group by town_from, town_to

注意：如果我理解有误，请纠正我，但是在您期望的输出中，伦敦<->新加坡的总数应该为8而不是4。您有4个单程旅行和4个往返旅行，总共为8个。

然后我们可以使用相同的查询来找到最多的航班数量，然后找到具有该数量的路线，然后进行计数。我猜您已经掌握了这部分内容，但这留作练习。

- lc.

---

基本上，逻辑是根据字母顺序切换位置字段，然后将其作为基础表。 - RoMEoMusTDiE

@lc。这正是我在寻找的！感谢您的解释。我想出了一种通过使用case语句连接town_from和town_to来回答问题的方法，但是您的示例更加简洁，也是我所需要的。再次感谢。我已经为您的答案点赞，但我的声望太低了:D。 - Uncle Jesse

Answer 2

select a.town_from, a.town_to, count(a.plane) ta from (
select plane, 
(case when town_from < town_to then town_from else town_to end) as town_from, 
(case when town_from < town_to then town_to else town_from end) as town_to 
from trip) as a
group by a.town_from, a.town_to
having count(a.plane) >= all (select count(plane) from trip group by plane, town_from, town_to)) s```

- Nomis