一、题目来源
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 (SELECTstate,group_concat(city order by city SEPARATOR ', ') as cities,COUNT(city) cnt1FROM citiesGROUP BY state
),t2 as(selectstate,count(city) as cnt2from citieswhere left(city,1) = left(state,1)group by state
)
SELECTdistinctt1.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;
七、总结
本题需求为查询每个州的城市:
难点在于将每个州原本是列数据的城市,使用逗号分隔为一行数据输出。我们使用了 Group_Concat 聚合函数解决这个问题;
Group_Concat 函数是一个聚合函数,它可以将分组后的某一列的值按照指定的分隔符连接成一个字符串。在一些情况下,我们可以利用它来将多列的数据转换为一行中的字符串形式,虽然不是严格意义上的列转行,但在某些展示场景下是很有用的。
获取每个州以及城市的首字母使用了 Left 函数;
Left() 函数是一个用于字符串操作的函数。它的主要功能是从一个字符串的左边开始提取指定长度的子字符串,基本语法为 :Left(str , length)。其中str
是要操作的原始字符串,length
是要从左边提取的子字符串的长度。