代码之家 › 专栏 › 技术社区 › shaedrich Gabin traverse

从起降场获得十大机场名称

sql-server sql

shaedrich Gabin traverse · 技术社区 · 6 年前

我的设想似乎很简单,但当我试图意识到这一点时,情况变得复杂起来:我想为客户提供他“最喜欢的机场”。

也许我太傻了没注意到

COUNT 这个 Start Landing 每个机场 Bookings
得到 TOP 10 机场 ID 计数 习惯于
Name 从 Airport

表和列

起点机场: int FOREIGN KEY Airports.ID
降落机场: 国际外键机场.ID

机场

身份证件: int PRIMARY KEY
姓名: varchar

当前状态

SELECT
    ttl.Start_Airport,
    ttl.Landing_Airport
FROM
    (
        SELECT
            COUNT(Start_Airport) AS Start_Airport_Count,
            COUNT(Landing_Airport) AS Landing_Airport_Count,
            Start_Airport, Landing_Airport
        FROM
            Bookings
        WHERE
            (
                Start_Airport <> 0 OR
                Landing_Airport <> 0
            )
        GROUP BY
            Start_Airport, Landing_Airport
        ) AS ttl
HAVING
  ttl.Start_Airport_Count = MAX(ttl.Start_Airport_Count) OR
  ttl.Landing_Airport_Count = MAX(ttl.Landing_Airport_Count)

现在它说 Start_Airport_Count 和 Landing_Airport_Count 必须在里面 GROUP BY 但是我得到的结果和我预期的不一样。

提前谢谢!

3 回复 | 直到 6 年前

Gordon Linoff 6 年前

我将解开并汇总:

select top (10) a.name, count(*)
from books b cross apply
     (values (Start_Airport), (Landing_Airport)) v(airport) join
     airports a
     on v.airport = a.id
group by a.name
order by count(*) desc;

我敢肯定这是最短最有效的方法。

Oto Shavadze 6 年前

如果我正确理解的话,你需要这样的东西:

SELECT Airports.name as Airport, t1.cnt as Start_Airport_count, t2.cnt as Landing_Airport_count
FROM (
    select Start_Airport as a_id, count(*) as cnt from  Bookings 
    group by Start_Airport 
) t1
FULL JOIN (
    select Landing_Airport as a_id, count(*) as cnt from  Bookings 
    group by Landing_Airport
) t2
ON t1.a_id =  t2.a_id 
INNER JOIN Airports
ON coalesce(t1.a_id, t2.a_id) = Airports.id
ORDER BY coalesce(t1.cnt,0) + coalesce(t2.cnt,0) DESC 
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY

Dave C 6 年前

我创建简单的变量表,计算开始/着陆的次数,然后按降序排列这些次数(最高=1)。

DECLARE @Bookings TABLE (Start_Airport INT, Landing_Airport INT)
DECLARE @Airports TABLE (Id INT, Name VARCHAR(50))

INSERT INTO @Airports(Id,Name)
VALUES (1,'New York'),
       (2,'Miami'),
       (3,'London'),
       (4,'Sydney'),
       (5,'Tokyo'),
       (6,'Los Angeles')

INSERT INTO @Bookings (Start_Airport,Landing_Airport)
VALUES (1,2),
       (1,3),
       (6,4),
       (6,5),
       (3,2),
       (3,4),
       (3,6),
       (5,3),
       (5,2),
       (4,2)

;WITH X AS
(
    SELECT *, COUNT(2) OVER(PARTITION BY Start_Airport) AS numOfDepartures,
              COUNT(2) OVER(PARTITION BY Landing_Airport) AS numofArrivals
    FROM @Bookings
), Y AS 
    (
        SELECT *, RANK() OVER(ORDER BY numOfDepartures DESC) AS rankDepartures,
                  RANK() OVER(ORDER BY numofArrivals DESC) AS rankArrival
        FROM X
    )

SELECT DISTINCT A.Name, numOfDepartures as flightCount, rankDepartures as airportRank, 'Top Departing Location' as Info
FROM Y 
JOIN @Airports A ON Y.Start_Airport=A.Id
WHERE rankDepartures=1
UNION
SELECT DISTINCT A.Name, numofArrivals, rankArrival, 'Top Arriving Location'
FROM Y 
JOIN @Airports A ON Y.Landing_Airport=A.Id
WHERE rankArrival=1
ORDER BY Info, Name