How do I query for records with multiple values for the last column in a compound GROUP BY clause?

Question

Given data that looks similar to this:

+---------+-----------+----------+
| country | city      | district |
+---------+-----------+----------+
| Japan   | Tokyo     | 1        |
| Japan   | Tokyo     | 1        |
| Japan   | Tokyo     | 2        |
| China   | Shanghai  | A        |
| China   | Shanghai  | A        |
| China   | Shanghai  | A        |
| China   | Beijing   | X        |
| China   | Beijing   | Y        |
| China   | Beijing   | Z        |
| India   | Mumbai    | 123      |
| India   | Mumbai    | 123      |
| India   | Mumbai    | 123      |
| India   | New Delhi | 321      |
| India   | New Delhi | 321      |
| India   | New Delhi | 321      |
+---------+-----------+----------+

I know I can get the data visually by first doing:

SELECT * from that_table
GROUP BY country, city, district

and I'd get:

+---------+-----------+----------+
| country | city      | district |
+---------+-----------+----------+
| Japan   | Tokyo     | 1        |
| Japan   | Tokyo     | 2        |
| China   | Shanghai  | A        |
| China   | Beijing   | X        |
| China   | Beijing   | Y        |
| China   | Beijing   | Z        |
| India   | Mumbai    | 123      |
| India   | New Delhi | 321      |
+---------+-----------+----------+

where I can see that only Japan/Tokyo and China/Beijing have multiple values for District. However, I have a huge source of data and I'd like to do that in SQL.

How do I form the SQL query to get all Country/City combinations with multiple Districts?

The output I'd like to achieve is:

+---------+-----------+----------+
| country | city      | district |
+---------+-----------+----------+
| Japan   | Tokyo     | 1        |
| Japan   | Tokyo     | 2        |
| China   | Beijing   | X        |
| China   | Beijing   | Y        |
| China   | Beijing   | Z        |
+---------+-----------+----------+

Answer 1

SELECT DISTINCT country, city, district FROM that_table tt1
              JOIN (
                    SELECT country, city  from that_table
                    GROUP BY country, city
                    HAVING count(1) > 1) tt2
              ON tt1.city = tt2.city
              AND tt1.Country = tt2.country

Answer 2

SELECT a.*
FROM foo a
  JOIN (
    SELECT country, city
    FROM (SELECT distinct country, city, district FROM foo)
    GROUP BY country, city
    HAVING count(country) > 1) b
  ON a.city = b.city AND a.country = b.country
GROUP BY a.country, a.city, a.district

Result:

COUNTRY CITY DISTRICT
Japan   Tokyo 2
Japan   Tokyo 1
China   Beijing Z
China   Beijing Y
China   Beijing X

Answer 3

You could add a condition that there must be at least one district for the same city with a different name:

SELECT a.*
FROM YourTable a
WHERE EXISTS (
    SELECT *
    FROM YourTable b
    WHERE a.Country = b.Country
    AND a.City = b.City
    AND a.District <> b.District
)