在 SQL 的子查询中选择最大值 [英] Select max value in subquery in SQL

问题描述

我有一个如下查询:

select * 
from 
    (select 
         centre_name, sum(qty) as number1 
     from 
         (select 
              exchange_from_centre_id as cenid, 
              count(exchange_from_centre_id) as qty
          from 
              as2.exchange
          group by 
              exchange_from_centre_id

          union all

          select 
              exchange_to_centre_id as cenid, 
              count(exchange_to_centre_id) as qty
          from 
              as2.exchange
          group by 
              exchange_to_centre_id), as2.centre c
where 
    c.centre_id = cenid
group by 
    centre_name);

结果如下:中心名称和交换次数

and this is the result: Name of the centre and the number of exchange

Alice Springs Desert Park   1
Werribee Open Range Zoo     6
Kruger National Park        2
Johannesburg Zoo            4
Australia Zoo               2
SanWild Wildlife Sanctuary  5

我喜欢从这个结果(第二行)中选择最大值，除了排序和选择第一行之外，任何人都可以帮助我进行 MAX 查询.

I like to select the max value from this result (the 2nd row), beside sorting and choosing the 1st row, could anyone help me with the MAX query.

推荐答案

应该可以的

select * from (select centre_name, sum(qty) as number1 from 
                (select exchange_from_centre_id as cenid, count(exchange_from_centre_id) as qty
                from as2.exchange
                group by exchange_from_centre_id
            union all
                select exchange_to_centre_id as cenid, count(exchange_to_centre_id) as qty
                from as2.exchange
                group by exchange_to_centre_id), as2.centre c
where c.centre_id = cenid
group by centre_name) where number1 = (select max(number1) from (select centre_name, sum(qty) as number1 from 
                (select exchange_from_centre_id as cenid, count(exchange_from_centre_id) as qty
                from as2.exchange
                group by exchange_from_centre_id
            union all
                select exchange_to_centre_id as cenid, count(exchange_to_centre_id) as qty
                from as2.exchange
                group by exchange_to_centre_id), as2.centre c
where c.centre_id = cenid
group by centre_name));

这篇关于在 SQL 的子查询中选择最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！