3198. Find Cities in Each State π
Description
Table: cities
+-------------+---------+ | Column Name | Type | +-------------+---------+ | state | varchar | | city | varchar | +-------------+---------+ (state, city) is the primary key (combination of columns with unique values) for this table. Each row of this table contains the state name and the city name within that state.
Write a solution to find all the cities in each state and combine them into a single comma-separated string.
Return the result table ordered by state
and city
in ascending order.
The result format is in the following example.
Example:
Input:
cities table:
+-------------+---------------+ | state | city | +-------------+---------------+ | California | Los Angeles | | California | San Francisco | | California | San Diego | | Texas | Houston | | Texas | Austin | | Texas | Dallas | | New York | New York City | | New York | Buffalo | | New York | Rochester | +-------------+---------------+
Output:
+-------------+---------------------------------------+ | state | cities | +-------------+---------------------------------------+ | California | Los Angeles, San Diego, San Francisco | | New York | Buffalo, New York City, Rochester | | Texas | Austin, Dallas, Houston | +-------------+---------------------------------------+
Explanation:
- California: All cities ("Los Angeles", "San Diego", "San Francisco") are listed in a comma-separated string.
- New York: All cities ("Buffalo", "New York City", "Rochester") are listed in a comma-separated string.
- Texas: All cities ("Austin", "Dallas", "Houston") are listed in a comma-separated string.
Note: The output table is ordered by the state name in ascending order.
Solutions
Solution 1: Grouping and Aggregation
We can first group by the state
field, then sort the city
field within each group, and finally use the GROUP_CONCAT
function to concatenate the sorted city names into a comma-separated string.
1 2 3 4 5 6 7 |
|
1 2 3 4 5 6 7 8 9 10 11 |
|