力扣-Mysql-3328-查找每个州的城市 II（中等）

一、题目来源

3328. 查找每个州的城市 II - 力扣（LeetCode）

二、数据表结构

表：cities

+-------------+---------+
| Column Name | Type    | 
+-------------+---------+
| state       | varchar |
| city        | varchar |
+-------------+---------+
(state, city) 是这张表中值互不相同的列的组合。
这张表的每一行包含州名和其中的城市名。

三、需求

编写一个解决方案来找到 每个州 中的 所有城市 并且根据下列条件分析它们：

用 逗号分隔 字符串组合每一个州的所有城市。
只显示有至少 3 个城市的州。
只显示 至少有一个城市 以与 州名相同字母开头 的州。

返回结果表以字母匹配城市的数量降序排序，然后按州名称升序排序的结果表。

四、示例数据

输入：

cities 表：

+--------------+---------------+
| state        | city          |
+--------------+---------------+
| New York     | New York City |
| New York     | Newark        |
| New York     | Buffalo       |
| New York     | Rochester     |
| California   | San Francisco |
| California   | Sacramento    |
| California   | San Diego     |
| California   | Los Angeles   |
| Texas        | Tyler         |
| Texas        | Temple        |
| Texas        | Taylor        |
| Texas        | Dallas        |
| Pennsylvania | Philadelphia  |
| Pennsylvania | Pittsburgh    |
| Pennsylvania | Pottstown     |
+--------------+---------------+

输出：

+-------------+-------------------------------------------+-----------------------+
| state       | cities                                    | matching_letter_count |
+-------------+-------------------------------------------+-----------------------+
| Pennsylvania| Philadelphia, Pittsburgh, Pottstown       | 3                     |
| Texas       | Dallas, Taylor, Temple, Tyler             | 3                     |
| New York    | Buffalo, Newark, New York City, Rochester | 2                     |
+-------------+-------------------------------------------+-----------------------+

解释：

Pennsylvania:
- 有 3 个城市（符合最低条件）
- 所有的 3 个城市都以 'P' 开头（与州相同）
- matching_letter_count = 3
Texas:
- 有 4 个城市（符合最低条件）
- 3 个城市 (Taylor, Temple, Tyler) 以 'T' 开头（与州相同）
- matching_letter_count = 3
New York:
- 有 4 个城市（符合最低条件）
- 2 个城市 (Newark, New York City) 以 'N' 开头（与州相同）
- matching_letter_count = 2
California 没有包含在输出表，因为：
- 尽管它有 4 个城市（符合最低条件）
- 没有城市以 'C' 开头（不符合字母匹配条件）
注意：
结果以 matching_letter_count 降序排序。
当 matching_letter_count 持平（Texas 和 New York 都为 2），按州名字母序排序。
每一行的城市也以字母序排序。

五、分析

1.文字分析

第一步：首先使用group_concat函数将每个州的城市使用逗号连接，并统计每个州的城市数量；

第二步：使用 left 获取城市名和州名的首字母，让它们相同，并统计城市名首字母和州名首字母相同的城市数量；

第三步：连接前两步得到的表，进行最终筛选，使cnt1>=3, cnt2>=1,cnt2即为所有的matching_letter_count,最终再根据m_l_c 降序，州名升序进行排列。

2.图解

六、代码实现

with t1 AS (
    SELECT
        state,
        group_concat(city order by city SEPARATOR ', ') as cities,
        COUNT(city) cnt1
    FROM cities
    GROUP BY state
),t2 as(
    select
        state,
        count(city) as cnt2
    from cities
    where left(city,1) = left(state,1)
    group by state
)
SELECT
    distinct
    t1.state,
    t1.cities,
    cnt2 as matching_letter_count
from t1,t2
where t1.state = t2.state and cnt1 >= 3 and cnt2 >= 1
ORDER BY matching_letter_count desc ,t1.state;