如果MAX()等于当前行和()合计值，如何选择1 ?

I'd like to select a new column named sliced (value can be 1/0 or true/false it doesn't matter) if area of the current row equals MAX(SUM(c.area)), that is flag the row with highest aggregate value:

我想选择一个新的列，叫做slice(值可以是1/0或者true/false，没关系)，如果当前行的面积等于MAX(SUM(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

I've tried adding to the selection area = MAX(area) AS sliced or even area = SUM(MAX(c.area)) AS sliced but i'm getting a syntax error. I've to admit i'm not so good in SQL. Thank you.

我已经尝试将选择区域= MAX(area)添加为切片，甚至是area = SUM(MAX(c.area))添加为切片，但我得到了一个语法错误。我得承认我的SQL不行。谢谢你！

3 个解决方案

#1

Here's a way to do it with just one group by:

这里有一个方法，只用一组by:

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;

The inner query (t1) does the group by and orders by total area largest first.
The next query (t2) gives the first row a value of true for column sliced, all other rows false.
The outer query orders the rows in the way you want - by name.

内部查询(t1)首先按区域总面积最大的顺序执行分组。下一个查询(t2)为第一行提供了一个值为true的列切片，所有其他行都为false。外部查询按您想要的方式(按名称)对行进行排序。

Since there's only one table scan and group by, this should be very efficient.

因为只有一个表扫描和组，这应该是非常有效的。

#2

As I understand your question, this should do it. Creates a pseudo-column which returns 1 when the area is the same as max(area) without any conditions to restrict your selection.

就像我理解你的问题一样，这个应该可以。创建一个伪列，当区域与max(区域)相同时返回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 As @Glide says you can't perform nested aggregation so sum(max(area)) won't work and you need to perform these operations one query at a time.

@Glide说您不能执行嵌套聚合，因此sum(max(area))不能工作，您需要每次执行一个查询。

#3

As comments have mentioned, you'd have to check all the values against another query. This is normal practice in SQL.

正如注释中提到的，您必须针对另一个查询检查所有值。这是SQL中的常规做法。

SELECT
  p.name AS name,
  SUM(c.area) AS area,
  CASE WHEN SUM(c.area) = (SELECT MAX(area) FROM <repeat your query here>) THEN 1 ELSE 0 END
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

The biggest downside to this is that you've had to repeat the code, which is just messy and a maintenance headache.

最大的缺点是您必须重复代码，这是非常混乱和维护麻烦的。

The alternative is to insert all the data into a temporary table, with the sliced field being 0 for all records. Then update that table, setting sliced to 1 for the record(s) with the highest area.

另一种方法是将所有数据插入临时表中，所有记录的分割字段为0。然后更新该表，将最高区域的记录分割为1。

#1