如何在没有不需要的值的情况下在组中包含列

Okay, this might sound lame but what I am going to ask may look a bit kiddish. But still I would always prefer to spill my heart out here than anywhere else.

好吧，这可能听起来有点扯，但我要问的可能有点孩子气。但我还是宁愿把我的心倾诉在这里，而不是其他任何地方。

So I have a table called TEMP_CALC which is as follows :

我有一个叫做TEMP_CALC的表，如下所示:

  COUNTRY   CITY         TEMP
=============================
   US      Arizona       51.7
   US      California    56.7
   US      Bullhead City 51.1
   India   Jaisalmer     42.4
   Libya   Aziziya       57.8
   Iran    Lut Desert    70.7
   India   Banda         42.4

So, the table consists of sample data for some temperature ratings in regions (just some test data).

因此，这个表格包含了一些区域的温度等级的样本数据(只是一些测试数据)。

What I want is a query that would display the cities with maximum temperatures along with their country name and the temperature rating itself.

我想要的是一个查询，该查询将显示具有最高温度的城市，以及它们的国家名称和温度评级本身。

The query I made would either give me the Country name and maximum temperature or gives me all the rows in the table. What I tried so far is as follows:

我所做的查询要么给出国家名称和最高温度，要么给出表中的所有行。我到目前为止所做的尝试如下:

This gives me only COUNTRY and TEMP -

这给了我唯一的国家和临时工

****************************
select country,max(temp) 
from temp_calc
group by country

And when I try to include the city name attached to these temperatures then it gives me all the rows in the table -

当我试着把城市的名字和这些温度联系起来的时候它就给了我表格中的所有行

*************************
select country,city,max(temp) 
from temp_calc
group by country,city

I want the city with the maximum temperature along with the country name and the temperature rating itself.

我想要有最高温度的城市，以及国家名称和温度等级本身。

3 个解决方案

#1

If I understand you correctly, you want the city with the highest temperature for each country. This is typically done using window functions:

如果我理解正确的话，你想要的是每个国家气温最高的城市。这通常是使用窗口函数完成的:

select country,city,temp
from (
  select country,city,temp, 
         row_number() over (partition by country order by temp desc) as rn
  from temp_calc
) t 
where rn = 1
order by country, city;

#2

You have the solution in your first try:

你在第一次尝试的时候就有了答案:

select country,max(temp) 
from temp_calc
group by country

You just need to add the corresponding city to the result set. You could do this:

您只需将相应的城市添加到结果集中，您可以这样做:

 with max_temp as (select country, max(temp) m_temp 
    from temp_calc
    group by country)
 select country, city, temp 
 from temp_calc where 
 temp=max_temp.m_temp and 
 country=max_temp.country;

This should allow you to filter the results from the table based on the maximum temps.

这应该允许您根据最大温度从表中过滤结果。

#3

select * from (
select *,  ROW_NUMBER() over (partition by country order by country,temp desc) as sortid, 
 from temp_calc  ) t 
 where sortid =1

#1