mysql - SQL 总数

Question

+--------+-------+----------+-----------+
| Maker  | Model | SeatType | NoOfSeats |
+--------+-------+----------+-----------+
| Airbus | 340   | E        |       220 |
| Airbus | 340   | F        |        20 |
| Airbus | 380   | E        |       300 |
| Airbus | 380   | F        |        60 |
| Boeing | 747   | E        |       300 |
| Boeing | 747   | F        |        40 |
| Boeing | 777   | E        |       200 |
| Boeing | 777   | F        |        20 |
| Boeing | 787   | E        |       250 |
| Boeing | 787   | F        |        25 |
+--------+-------+----------+-----------+

下面是我尝试创建这个“PlaneSeats”表的查询

select Maker, count(distinct Model)
from PlaneSeats 
group by Maker, Model 
having SUM(NoOfSeats) > 350;

这是查询结果

+--------+-----------------------+
| Maker  | count(distinct Model) |
+--------+-----------------------+
| Airbus |                     1 |
+--------+-----------------------+

但我想得到的是制造商和该制造商制造的飞机总数，也适用于制造总座位数超过 350 的飞机的制造商。我知道这里的计数应该是 2 而不是 1，因为Maker Airbus 有 2 个模型。请帮我找出哪里错了。谢谢。

Answer 1

我认为您需要两个级别的聚合:

select maker, count(*)
from (select maker, model, sum(NoOfSeats) as numseats
      from planeseats ps
      group by maker, model
     ) mm
where numseats > 350
group by maker;

编辑:

当一架飞机足够大时，您希望所有飞机都算作制造。这意味着将 where 转换为 having 子句:

select maker, count(*)
from (select maker, model, sum(NoOfSeats) as numseats
      from planeseats ps
      group by maker, model
     ) mm
group by maker
having max(numseats) > 350;