在 SQL 的子查询中选择最大值

本文介绍了在 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));