sql - 从表中编写查询的问题

Question

我正在尝试为家庭作业编写一个查询，但有一个查询特别让我遇到了麻烦。我遇到麻烦的那个应该得到所有形式的政府从一张表中，然后算出有多少国家拥有这种形式的政府。我知道我必须使用计数功能，但我不明白我会怎么做每提到政府一次加 1。此外，独立年的事情也让我感到困惑。这是查询的确切定义

Generate a list of all forms of government with the count of how many countries have that form of government. Also, list the most recent year in which any country became independent with that form of government. The results should be ordered by decreasing count.

这是我尝试过的代码，以及我尝试过的表格

SELECT government_form, COUNT(government_form) AS count, indep_year 
FROM what.country
ORDER BY count DESC 

这是我正在使用的表格

               Table "what.country"
     Column      |         Type          |               Modifiers              
-----------------+-----------------------+--------------------------------------
 country_code    | character(3)          | not null default ''::bpchar
 name            | character varying(52) | not null default ''::character varying
 continent       | continent             | not null
 region          | character varying(26) | not null default ''::character varying
 surface_area    | real                  | not null default 0::real
 indep_year      | smallint              | 
 population      | integer               | not null default 0
 life_expectancy | real                  | 
 gnp             | real                  | 
 gnp_old         | real                  | 
 local_name      | character varying(45) | not null default ''::character varying
 government_form | character varying(45) | not null default ''::character varying

Answer 1

Generate a list of all forms of government with the count of how many countries have that form of government. Also, list the most recent year in which any country became independent with that form of government. The results should be ordered by decreasing count.

首先让我们分部分理解问题

count of how many countries have that form of government

意味着我们需要GROUP BY government_form 然后在国家上应用COUNT

most recent year in which any country became independent with that form of government

意味着我们还需要在按 government_form 分组后找到 MAX indep_year

因此查询是

SELECT government_form, COUNT(countries) AS count, MAX(indep_year)
FROM what.country
GROUP BY government_form
ORDER BY count DESC