我想选择一个名为sliced的新列(值可以是1/0true/false这无关紧要),如果当前行的area等于MAX(SUM(c.area)),则标记聚合值最高的行:

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不太好。谢谢您。

最佳答案

这里有一种方法,只需要一组就可以做到:

set @row := 0;
select name, area, sliced
from (
    select name, area, (@row := @row + 1) = 1 as sliced
    from (
        SELECT p.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 1
        ORDER BY 2 desc) t1
    ) t2
order by 1;

内部查询(t1)首先执行“分组依据”和“按总面积排序”。
下一个查询(t2)为第一行的列true和所有其他行sliced提供值false
外部查询按所需的方式按名称对行进行排序。
因为只有一个表扫描和分组,所以这应该非常有效。

关于mysql - 如果MAX()等于当前行SUM()聚合值,MySQL如何选择1?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/7725337/

10-13 07:53