聚合上的MySQL语法查询

Question

我有一个名为 TICKET 的表，其中包含 FLIGHTNUMBER 和 INSURED 列。 INSURED 只有 0 或 1 个值。 FLIGHTNUMBER 有重复项。我想在 INSURED 中获得最大数量为 1 的 FLIGHTNUMBER。这是我尝试过的:

SELECT FLIGHTNUMBER 
FROM (SELECT FLIGHTNUMBER,SUM(INSURED) AS INSURANCE_COUNT 
      FROM TICKET 
      GROUP BY TICKET.FLIGHTNUMBER) AS OUT2 
WHERE OUT2.INSURANCE_COUNT = MAX(INSURANCE_COUNT) ;

和

SELECT FLIGHTNUMBER 
FROM (SELECT FLIGHTNUMBER,SUM(INSURED) AS INSURANCE_COUNT 
      FROM TICKET 
      GROUP BY TICKET.FLIGHTNUMBER 
      HAVING INSURANCE_COUNT = MAX(SUM(INSURED))) AS OUT2;

但两者都会导致错误。你能告诉我正确的语法吗？提前致谢。

Answer 1

第一个不起作用，因为您不能在 WHERE 子句中使用聚合函数。直到所有行都被选中后才会发生聚合，并且 WHERE 用于选择行，因此存在先有鸡还是先有蛋的问题。

第二个不起作用，因为您不能嵌套聚合函数。

您不需要计算 MAX(INSURANCE_COUNT)。只需对查询进行排序并取第一个。

SELECT flightnumber
FROM (SELECT flightnumber, SUM(INSURED) AS insurance_count
      FROM ticket
      GROUP BY flightnumber) AS out2
ORDER BY insurance_count DESC
LIMIT 1

您甚至不需要 SELECT 列表中的 insurance_count，因此您不需要将其包装在子查询中以将其删除。

SELECT flightnumber
FROM ticket
GROUP BY flightnumber
ORDER BY SUM(insured) DESC
LIMIT 1

但是，如果有多个航类与最大值并列，这将只返回其中一个。如果您需要获取所有这些，则需要编写一个获取最大值的子查询，并与之连接。

SELECT flightnumber
FROM (SELECT flightnumber, SUM(insured) AS insurance_count
      FROM ticket
      GROUP BY flightnumber) AS out2
JOIN (SELECT SUM(insured) AS max_insurance_count
      FROM ticket
      GROUP BY flightnumber
      ORDER BY max_insurance_count DESC
      LIMIT 1) AS out3
ON out2.insurance_count = out3.max_insurance_count