解析 SQL 表中以破折号分隔的值并进行查找 [英] Parsing a dash-delimited value in a SQL table and doing lookups

问题描述

如何解析 SQL 表中以破折号分隔的值并使用不同的解析出的值在其他表中进行查找?

How do I parse a dash-delimited value in a SQL table and use the different parsed-out values to do lookups in other tables?

假设我有 users.nationality，它是一个类似于41-33-11"的 varchar.我有国家、城市和州表，这些 ID 分别为 41(Buffalo")、33(NY")和 11(USA").

Say I have users.nationality, which is a varchar like "41-33-11". I have the country, city and state tables keyed to those ids, 41 ("Buffalo"), 33 ("NY") and 11 ("USA").

SELECT users.name, users.email, users.nationality FROM users

SELECT users.name, users.email, users.nationality FROM users

国籍 = "41-33-11"

nationality = "41-33-11"

如何制作 SELECT 语句以获取Buffalo, NY, USA"?

How do I craft a SELECT statement so to get "Buffalo, NY, USA"?

推荐答案

使用内联视图提取值，以便随后加入它们:

Use an inline view to pull out the values so you can then join onto them:

JOIN (SELECT SUBSTR(t.nationality, 0, 2) AS city_id,
             SUBSTR(t.nationality, INSTR(t.nationality, '-')+1, 2) AS state_id,
             RIGHT(t.nationality, 2) AS country_id
        FROM USERS t) u
JOIN COUNTRY cntry ON cntry.country_id = u.country_id
JOIN CITY cty ON cty.city_id = u.cty_id
JOIN STATE st ON st.state_id = u.state_id

这篇关于解析 SQL 表中以破折号分隔的值并进行查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！