mysql、@variable 和 if 语句

Question

各位，我遵循此link的指示

我创建了这样的表

CREATE TABLE cities
(
  city VARCHAR(80),
  country VARCHAR(80),
  population INT
);

INSERT INTO cities VALUES ('New York', 'United States', 8175133); 
INSERT INTO cities VALUES ('Los Angeles', 'United States', 3792621); 
INSERT INTO cities VALUES ('Chicago', 'United States', 2695598); 

INSERT INTO cities VALUES ('Paris', 'France', 2181000);
INSERT INTO cities VALUES ('Marseille', 'France', 808000);
INSERT INTO cities VALUES ('Lyon', 'France', 422000);

INSERT INTO cities VALUES ('London', 'United Kingdom',  7825300);
INSERT INTO cities VALUES ('Birmingham', 'United Kingdom', 1016800);
INSERT INTO cities VALUES ('Leeds', 'United Kingdom', 770800);     

当我运行此查询时

SELECT city, country, population
  FROM
  (SELECT city, country, population, 
              @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
              @current_country := country 
    FROM cities
    ORDER BY country, population DESC
   ) ranked
   WHERE country_rank <= 2;

它没有给我每个国家/地区 2 个最大的城市

是不是我漏掉了什么？谢谢

Answer 1

在您的代码前添加以下两行代码，即可运行。

SET @current_country:=NULL;
SET @country_rank:=0;


SELECT city, country, population
  FROM
  (SELECT city, country, population, 
             @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
          @current_country := country 
  FROM cities
  ORDER BY country, population DESC
   ) ranked
   WHERE country_rank <= 2;