假设我有两个表- routes和country_codes。表routes包含列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
,以给予结果前缀country
我要找的结果
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查询来完成此操作?
复杂的是它不是一个可以用联接完成的直接匹配。它的最长匹配需要使用。
2条答案
按热度按时间p1tboqfb1#
您必须检查
routes
中的prefix
及其所有子字符串,并使用COALESCE()
,直到获得country_codes
中的行vh0rcniy2#
执行
JOIN
以获取所有匹配项。对于每个前缀,使用窗口函数根据国家a地区代码的长度对匹配项进行排序。然后仅保留每个前缀的第一行。