从table1中选择顶部列，用它们的名称连接table2

Question

我有一个包含这两列的TABLE1，存储来自航班的出发和到达标识符：

dep_id  arr_id

   1    2
   6    2
   6    2
   6    2
   6    2
   3    2
   3    2
   3    2
   3    4
   3    4
   3    6
   3    6

和一个包含其国际民航组织代码的ID的TABLE2：

id  icao
1   LPPT
2   LPFR
3   LPMA
4   LPPR
5   LLGB
6   LEPA
7   LEMD

如何选择TABLE1的最高计数(最常用的出发id和最常用的到达id)，并将其与来自TABLE2的国际民航组织代码进行分组，以便从提供的示例数据中获得：

most_arrivals   most_departures
    LPFR             LPMA

很容易得到其中之一，但混合两个或更多的列似乎不适合我，无论我尝试什么。

Answer 1

试试这个：

select
    (select name
        from table2 where id = (
        select top 1 arr_id
        from table1
        group by arr_id
        order by count(*) desc)
    )  as most_arrivals,
    (select name
        from table2 where id = (
        select top 1 dep_id
        from table1
        group by dep_id
        order by count(*) desc)
    ) as most_departures

Answer 2

你可以这样做。

创建并填充表。

CREATE TABLE dbo.Icao
(
    id int NOT NULL PRIMARY KEY,
    icao nchar(4) NOT NULL
);

CREATE TABLE dbo.Flight
(
    dep_id int NOT NULL
        FOREIGN KEY REFERENCES dbo.Icao(id),
    arr_id int NOT NULL
        FOREIGN KEY REFERENCES dbo.Icao(id)
);

INSERT INTO dbo.Icao (id, icao)
    VALUES
    (1, N'LPPT'),
    (2, N'LPFR'),
    (3, N'LPMA'),
    (4, N'LPPR'),
    (5, N'LLGB'),
    (6, N'LEPA'),
    (7, N'LEMD');

INSERT INTO dbo.Flight (dep_id, arr_id)
    VALUES
    (1, 2),
    (6, 2),
    (6, 2),
    (6, 2),
    (6, 2),
    (3, 2),
    (3, 2),
    (3, 2),
    (3, 4),
    (3, 4),
    (3, 6),
    (3, 6);

然后使用两个子查询进行选择。

SELECT
    (SELECT TOP 1 I.icao
        FROM dbo.Flight AS F
            INNER JOIN dbo.Icao AS I
                ON I.id = F.arr_id
        GROUP BY I.icao
        ORDER BY COUNT(*) DESC) AS 'most_arrivals',
    (SELECT TOP 1 I.icao
        FROM dbo.Flight AS F
            INNER JOIN dbo.Icao AS I
                ON I.id = F.dep_id
        GROUP BY I.icao
        ORDER BY COUNT(*) DESC) AS 'most_departures';

​

​

在执行查询时，单击工具栏上的此按钮以包括实际的执行计划。

​

​

这是查询的图形执行计划。每个图标表示将由Server引擎执行的操作。箭头表示数据流。流的方向是从右到左，所以结果是最左边的图标。

​

​