我想选择一个名为sliced
的新列(值可以是1/0
或true/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/