sqlite - 在另一张桌子上查找最长的匹配号码
问题描述
鉴于我有两个表 - 路线和国家代码。表路由包含prefix
我想与表 country_codes 列中找到的最长值匹配的列code
目标是找到每条路线的国家名称。
我拥有的数据结构
CREATE TABLE routes(
prefix INTEGER NOT NULL,
supplier VARCHAR(64) NOT NULL
);
餐桌路线
prefix supplier
1876 att
1787 att
1 att
81 bt
8150 bt
8170 bt
8180 bt
8190 bt
82 verizon
821 verizon
84 att
84120 att
84121 att
84122 att
84123 att
84124 att
84125 att
85248 verizon
85249 verizon
85251 verizon
CREATE TABLE country_codes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
country VARCHAR(128) DEFAULT 0 NOT NULL,
code INTEGER
);
表国家代码
167 American Samoa 1684
170 Anguilla 1264
173 Antigua and Barbuda 1268
181 Bahamas 1242
184 Barbados 1246
189 Bermuda 1441
195 British Virgin Islands 1284
205 Cayman Islands 1345
225 Dominica 1767
226 Dominican Republic 1809
227 Dominican Republic 1829
228 Dominican Republic 1849
251 Grenada 1473
253 Guam 1671
273 Jamaica 1876
310 Montserrat 1664
326 Northern Mariana Islands 1670
340 Puerto Rico 1787
341 Puerto Rico 1939
350 Saint Kitts and Nevis 1869
351 Saint Lucia 1758
354 Saint Vincent and the Grenadines 1784
364 Sint Maarten 1721
389 Trinidad and Tobago 1868
393 Turks and Caicos Islands 1649
399 United States 1
401 US Virgin Islands 1340
274 Japan 81
370 South Korea 82
405 Vietnam 84
201 Cambodia 855
261 Hong Kong 852
282 Laos 856
291 Macau 853
325 North Korea 850
所以我想查询表routes
给我结果前缀,国家
我正在寻找的结果
prefix country
1876 Jamaica
1787 Puerto Rico
1 United States
81 Japan
8150 Japan
8170 Japan
8180 Japan
8190 Japan
82 South Korea
821 South Korea
84 Vietnam
84120 Vietnam
84121 Vietnam
84122 Vietnam
84123 Vietnam
84124 Vietnam
84125 Vietnam
85248 Hong Kong
85249 Hong Kong
85251 Hong Kong
我可以使用什么 sql 查询来执行此操作?
复杂性不是可以通过连接完成的直接匹配。需要使用其最长的匹配。
解决方案
您必须检查prefix
fromroutes
及其所有子字符串并使用COALESCE()
,直到您从country_codes
SELECT
r.prefix,
COALESCE(
(SELECT country FROM country_codes c WHERE c.code = r.prefix),
(SELECT country FROM country_codes c WHERE c.code = SUBSTR(r.prefix, 1, 4)),
(SELECT country FROM country_codes c WHERE c.code = SUBSTR(r.prefix, 1, 3)),
(SELECT country FROM country_codes c WHERE c.code = SUBSTR(r.prefix, 1, 2)),
(SELECT country FROM country_codes c WHERE c.code = SUBSTR(r.prefix, 1, 1))
) AS country
FROM routes AS r;
推荐阅读
- python - Python ftplib 可以在特定时间保持 FTP 下载吗?
- javascript - 在组件中使用 getStaticProps
- python - Dataflow Flex 模板验证失败,没有给出任何理由
- c - CS50 Speller:valgrind 测试说字节仍然可以在 3 个块中到达
- android - Retrofit2:java.io.EOFException:第 1 行第 1 列路径 $ 输入结束
- swift - 在 iOS 15 上启动应用程序时观察“应用程序需要更新”消息
- javascript - 如何删除Firefox扩展中的存储值?
- database - Neo4j - Java 堆空间 java.lang.OutOfMemoryError: Java 堆空间
- regex - 如何将多个数字序列捕获为重复组?
- django - 使用下载按钮在 django 管理面板中下载模型数据