sqlite 在另一个表中查找最长匹配编号

bsxbgnwa 于 2022-11-24 发布在 SQLite

关注(0)|答案(2)|浏览(158)

假设我有两个表- 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查询来完成此操作？
复杂的是它不是一个可以用联接完成的直接匹配。它的最长匹配需要使用。

sqlite

来源：https://stackoverflow.com/questions/53960282/find-longest-match-number-on-another-table

2条答案

按热度按时间

p1tboqfb1#

您必须检查routes中的prefix及其所有子字符串，并使用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;

赞(0）回复(0）举报 2022-11-24

vh0rcniy2#

执行JOIN以获取所有匹配项。对于每个前缀，使用窗口函数根据国家a地区代码的长度对匹配项进行排序。然后仅保留每个前缀的第一行。

SELECT prefix
     , country
  FROM
   (
       SELECT prefix
            , country
            , row_number() OVER (PARTITION BY prefix ORDER BY length(code) DESC) AS row_number
         FROM routes 
    LEFT JOIN country_codes 
           ON prefix LIKE code || '%'
   )
 WHERE row_number = 1
;

赞(0）回复(0）举报 2022-11-24

我来回答

sqlite 在另一个表中查找最长匹配编号

2条答案

相关问题

热门标签

最新问答