mysql - SQL总数

+--------+-------+----------+-----------+
| 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，因为制造商空客有2种型号。请帮我找出哪里错了。谢谢您。

最佳答案

我认为你需要两个层次的聚合：

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;

关于mysql - SQL总数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42591524/