如果 MAX() 等于当前行 SUM() 聚合值，MySQL 如何选择 1？

Question

我想选择一个名为 sliced 的新列(值可以是 1/0 或 true/false 但它不是matter) 如果当前行的 area 等于 MAX(SUM(c.area))，即 flag 具有最高聚合值的行:

SELECT p.name AS name, SUM(c.area) AS area
FROM City AS c
   INNER JOIN Province AS p ON c.province_id = p.id
      INNER JOIN Region AS r ON p.region_id = r.id
WHERE r.id = ?
GROUP BY p.id
ORDER BY p.name ASC

我尝试将 area = MAX(area) AS sliced 或什至 area = SUM(MAX(c.area)) AS sliced 添加到选择中，但我出现语法错误。我不得不承认我的 SQL 不太好。谢谢。

Answer 1

据我了解您的问题，这应该可以做到。创建一个伪列，当面积与 max(area) 相同时返回 1，没有任何条件限制您的选择。

SELECT name
     , area
     , case area when max_area then 1 else 0 end as sliced
  FROM ( SELECT name
              , area
              , max(area) over (partition by 1) as max_area
           FROM ( SELECT p.name AS name
                       , SUM(c.area) AS area
                    FROM City AS c
                   INNER JOIN Province AS p ON c.province_id = p.id
                   INNER JOIN Region AS r ON p.region_id = r.id
                   WHERE r.id = ?
                   GROUP BY p.id
                   ORDER BY p.name ASC )
               )

EDIT 正如@Glide 所说，您不能执行嵌套聚合，因此 sum(max(area)) 将不起作用，您需要在一个查询中执行这些操作一次。