如何从 SQL 中的两列获取唯一对

Question

我被困在 http://www.sql-ex.ru/learn_exercises.php#answer_ref 的练习 68 上。

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

1. 公司(ID_comp，名称)
2. 行程(trip_no、id_comp、飞机、town_from、town_to、time_out、time_in)
3. 乘客(ID_psg，姓名)
4. Pass_in_trip(trip_no、日期、ID_psg、地点)

练习的目标是： 找出航班(行程)数量最多的航线数量。 笔记。

A - B

1. 和 B - A 将被视为同一路线。
2. 仅使用行程表。

我可以得到"找出航班(行程)数量最多的航线数量"的正确答案，但在考虑注释 1 时则不然：A - B 和 B - A 被视为同一路线。

我不知道如何获得唯一的对： 如果我们有输出：

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

如何选择，以便它只给我

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

我能够通过以下查询完成问题：

与 x AS( 选择 con， sum(c) 作为 s FROM( 选择town_from，town_to，"con"=较低时的情况(town_from)

从 X 中选择计数(*) 其中 s = (从 x 中选择最大值)

Answer 1

您需要以与表示to->>from 相同的方式查看Trip表。执行此操作的典型方法是确保它始终town_from <(=) town_to排序。

一般来说，以下查询将以这种方式投影Trip。case 子句在周围切换以使它们始终排序：

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 个。

然后，我们可以使用相同的查询来查找最大数量的航班，然后找到具有该编号的路线，然后计数。我怀疑你已经记下了这部分，但这只是一个练习。

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```