題目描述

Flights表

+-------------+------+?

| 列名 ? ? ? ? | 類型 |?

+-------------+------+?

| flight_id ? | int ?|?

| capacity ? ?| int ?|?

+-------------+------+?

flight_id 是該表的主鍵。 該表的每一行包含航班ID和它的座位容量。

Passengers表

+--------------+------+?

| Column Name ?| Type |

+--------------+------+?

| passenger_id | int ?|?

| flight_id ? ?| int ?|?

+--------------+------+?

passenger_id 是該表的主鍵列。 該表的每一行包含乘客ID和航班ID。

乘客提前預(yù)訂航班機票。如果乘客預(yù)訂了某個航班的機票，并且該航班還有空座位，乘客的機票將會得到?確認?。然而，如果航班已經(jīng)滿員，乘客將會進入?等待名單?。

編寫一條 SQL 查詢，報告每個航班已成功預(yù)訂（獲得座位）的乘客數(shù)以及處于等待名單上的乘客數(shù)。

按照航班 id 升序排序返回結(jié)果表。

查詢結(jié)果格式如下例所示。

?

示例 1：

輸入：

Flights table:?

+-----------+----------+?

| flight_id | capacity |?

+-----------+----------+?

| 1 ? ? ? ? | 2 ? ? ? ?|?

| 2 ? ? ? ? | 2 ? ? ? ?|

| 3 ? ? ? ? | 1 ? ? ? ?|

+-----------+----------+?

Passengers table:?

+--------------+-----------+

| passenger_id | flight_id |

+--------------+-----------+?

| 101 ? ? ? ? ?| 1 ? ? ? ? |

| 102 ? ? ? ? ?| 1 ? ? ? ? |

| 103 ? ? ? ? ?| 1 ? ? ? ? |?

| 104 ? ? ? ? ?| 2 ? ? ? ? |

| 105 ? ? ? ? ?| 2 ? ? ? ? |?

| 106 ? ? ? ? ?| 3 ? ? ? ? |?

| 107 ? ? ? ? ?| 3 ? ? ? ? |?

+--------------+-----------+

輸出：

+-----------+------------+--------------+?

| flight_id | booked_cnt | waitlist_cnt |

+-----------+------------+--------------+?

| 1 ? ? ? ? | 2 ? ? ? ? ?| 1 ? ? ? ? ? ?|

| 2 ? ? ? ? | 2 ? ? ? ? ?| 0 ? ? ? ? ? ?|

| 3 ? ? ? ? | 1 ? ? ? ? ?| 1 ? ? ? ? ? ?|

+-----------+------------+--------------+

解釋：

- 航班 1 的座位容量為 2。由于有 3 名乘客預(yù)訂了機票，只有 2 名乘客可以獲得座位。因此，有 2 名乘客成功預(yù)訂，并且有 1 名乘客在等待名單上。 - 航班 2 的座位容量為 2。由于有 2 名乘客預(yù)訂了機票，每個人都可以獲得座位。結(jié)果是，有 2 名乘客成功預(yù)訂了座位，且沒有乘客在等待名單上。 - 航班 3 的座位容量為 1。由于有 2 名乘客預(yù)訂了機票，只有 1 名乘客可以獲得座位。因此，有 1 名乘客成功預(yù)訂，并且有 1 名乘客在等待名單上。

解法

我們可以使用左連接將?Flights?和?Passengers?表連接起來，然后按照?flight_id?分組，統(tǒng)計每個航班的乘客數(shù)量。

對于每個航班，我們可以使用?count(passenger_id)?統(tǒng)計乘客數(shù)量，取?capacity?和?count(passenger_id)?的最小值作為已預(yù)訂的乘客數(shù)量，取?count(passenger_id) - capacity?和?0?的最大值作為等待名單上的乘客數(shù)量。

SQL