Postgresql使用聚合和非聚合列，分组依据

Some things in lecture and in my lab assignment were not explained very well. I am having trouble displaying the correct information. Here is the database info, simply a reference for you to help me. The database tables info This is the query that I am trying to execute The postgresql php select statement

讲座和实验室作业中的一些内容没有得到很好的解释。我无法显示正确的信息。这是数据库信息,只是您的帮助我的参考。数据库表信息这是我试图执行的查询postgresql php select语句

This results in this SQl error being throwned

这导致抛出此SQl错误

Connected to database! Query failed: ERROR: column "city.name" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: city.name ^

连接数据库!查询失败:错误:列“city.name”必须出现在GROUP BY子句中或用于聚合函数LINE 3:city.name ^

Now if I do add city.name into the GROUP BY clause, it returns 4096 rows! I dont want that to happen, the results have to be group by country name which is 232 rows. I simply want to display the country name, city name, and the city with the highest population in that country. City name is throwing me off, Im guessing there is a more complicated more syntax heavy solution. Thanks for any help you can provide. -Tom Reese

现在,如果我将city.name添加到GROUP BY子句中,它将返回4096行!我不希望这种情况发生,结果必须按国家名称分组,即232行。我只是想显示国家名称,城市名称以及该国家人口最多的城市。城市名称让我失望,我猜测有更复杂的语法重量级解决方案。感谢您的任何帮助,您可以提供。 -Tom Reese

1 个解决方案

#1

You need something like this:

你需要这样的东西:

select
        country.name,
        city.name,
        mp.maxpop
from
        lab6.country,
        lab6.city,
        (select
                country_code,
                max(population) as maxpop
        from
                lab6.city
        group by
                country_code
        ) mp
where
        country.country_code=mp.country_code and
        country.country_code=city.county_code and 
        mp.maxpop=city.population

notes:

This can give you more result/county.

这可以给你更多的结果/县。

Your original query doesn't work because in ansi sql you can't return only aggregated or group by expressions from a "group by" query. (As the error mentions)

您的原始查询不起作用,因为在ansi sql中,您不能仅通过“group by”查询中的表达式返回聚合或分组。 (正如错误提到的那样)

#1