如何编写这个SQL查询？

Question

我在想能否得到关于这个 SQL 查询的帮助。这是一个我没有通过的测试，现在想要学习它。我花了很多时间在上面，看了 SQL 的教程，但我仍然不知道该怎么写它。

有两个表（flights，occupation_flights）记录了航班信息，任务是：编写一个查询，列出未来7天的航班清单，并显示航班、可用座位数、已占用座位数和已占用座位的百分比。

FLIGHTS:                    
FLIGHT  ID_COMPANY   DATE      ORIGIN   DESTINATION PLACES
1003       D3      20/01/2006   MADRID  LONDRES       40
1007       AF      20/01/2006   PARIS   MALAGA        12
1003       15      30/01/2006   MADRID  LONDRES       20
1007       AF      30/01/2006   PARIS   MALAGA        17

（PLACES显示每个航班提供的座位数）

OCCUPATION_FLIGHTS:
FLIGHT ID_COMPANY   DATE    PASSENGER   SEAT
1003    IB       20/01/2006 07345128H   01V
1003    IB       20/01/2006 1013456213  01P
1003    IB       20/01/2006 08124356C   02V
1003    IB       20/01/2006 57176355K   02P
1007    AF       20/01/2006 27365138A   
1003    IB       30/01/2006 734512811   01V
1003    IB       30/01/2006 1013456213  02V
1003    IB       30/01/2006 57176355K   

当SEAT为空时，这意味着某人尚未发出机票。 测试数据 我认为要获得可用座位数，应该将每个航班和日期的乘客不为空的情况相加，并从PLACES中提取。所以我从以下内容开始：

SELECT 
f.flight, 
f.places - (SELECT (CASE WHEN of.passanger IS NOT NULL THEN 1 ELSE 0 END)      
FROM occupation_flights AS of GROUP BY of.flight, of.date) AS available,
f.places - (SELECT (CASE WHEN of.passanger IS NULL THEN 1 ELSE 0 END) FROM occupation_flights AS of GROUP BY of.flight, of.date) AS occupied,
100.0 * ((f.places - (SELECT (CASE WHEN of.passanger IS NOT NULL THEN 1 ELSE 0 END) FROM occupation_flights AS of GROUP BY of.flight, of.date)) / f.places AS %occupied
FROM flights AS f
JOIN occupation_flights AS of ON f.flight=of.flight
WHERE f.date BETWEEN DateAdd(DD,-7,GETDATE() ) and GETDATE() 
GROUP BY f.flight

但它还没有完成，因为我真的不明白如何让他同时按航班和日期分组计算，因为有相同航班号但在不同日期进行的航班。我也无法访问表格，这个测试是纸质的，只能展示你知道如何编写SQL查询语句。非常感谢您的帮助！

- Yana

---

我已在 sqlfiddle 中对你的数据进行了分组：http://sqlfiddle.com/#!9/d62a4 请随意编辑您的帖子，如添加“测试数据”，然后在其上链接此URL，以便其他人也可以获取这些数据。我也会尝试获取您需要的内容，但这并不是我的长项:p - Jeremy C.

你确定你正在使用MySQL吗？getdate和dateadd不是MySQL函数。 - Jeremy C.

嗨Jeremy，非常感谢你的回答。我现在会查看所有评论并尝试理解逻辑。非常感谢这个SQL Fiddle，我不知道这样的东西存在，它使事情变得更容易！我不知道我是否实际上正在使用MySQL，我以前在工作中总是使用pgAdmin Postgre或类似的东西，而这个任务只是一个我未能通过的SQL知识测试... - Yana

说实话，我也只知道它们大约一个月，所以没有什么羞耻的，直到我加入这个网站才知道有这样的东西存在。 - Jeremy C.

Answer 1

Mysql一致性：

SQLFIDDLE

SELECT f.flight,f.mydate,f.places - count(of.seat) as available_seats,
        count(of.seat) as occupied,
        count(of.seat)/f.places * 100 as percentage_occupied
FROM flights f
JOIN occupation_flights of
ON f.flight = of.flight AND f.mydate = of.mydate
WHERE of.mydate BETWEEN DATE_ADD(CURDATE(), INTERVAL -7 DAY)AND CURDATE() 
group by f.flight, f.mydate

- Jeremy C.

Answer 2

select t1.flight,
   t1.date,
   t1.places,
   t3.c,
   t1.places-IFNULL(t3.c, 0) as empty
from flights t1 
left join(
    select count(t2.seat) as c, t2.flight, t2.date
        from occupation_flights t2
    where seat is not null
        group by flight, date
        ) t3 
    on t1.flight= t3.flight and t1.date= t3.date

这是结果

PS. 在我的代码中，使用了一个 MySql 函数 IFNULL()，在另一个数据库实现中应该使用其他类似的函数进行替换。

- blindpirate

---

像 COALESCE() 这样的函数在 MySQL 中也可以使用，并符合 ANSI 标准。 - Strawberry

Answer 3

SELECT
    f.flight, f.mydate, f.id_company, origin, destination,
    places, (places - COALESCE(t.ocupped_seats, 0)) as available_places,
    IFNULL(t.ocupped_seats, 0) as ocupped_places,
    CONCAT(ROUND(COALESCE(places/COALESCE(t.ocupped_seats, 0), 0), 2), '%') as percentage
FROM
    flights as f
LEFT JOIN
    (SELECT
         flight, mydate,id_company, COUNT(seat) as ocupped_seats
     FROM
         occupation_flights
     WHERE
         seat IS NOT NULL
     GROUP BY
         flight, mydate) as t
     ON
         f.flight = t.flight AND f.mydate LIKE t.mydate
WHERE
    f.mydate BETWEEN CURDATE() AND CURDATE()+7

演示（感谢Jeremy C.构建模式和Strawberry提供COALLESCE函数）

结果：

- KM11