如何解析SQL表中由短划线定界的值并使用不同的解析出的值在其他表中进行查找？
说我有users.nationality，这是一个像“41-33-11”varchar。 我有国家，城市和州表格，其中41个（“布法罗”），33个（“纽约”）和11个（“美国”）。
SELECT users.name，users.email，users.nationality FROM users
国籍=“41-33-11”
如何制作SELECT语句以获得“美国纽约州布法罗”？
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?
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
nationality = "41-33-11"
How do I craft a SELECT statement so to get "Buffalo, NY, USA"?
最满意答案
使用内联视图提取值，以便可以加入它们：
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_idUse 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表中由短划线定界的值并进行查找(Parsing a dash-delimited value in a SQL table and doing lookups)如何解析SQL表中由短划线定界的值并使用不同的解析出的值在其他表中进行查找？
说我有users.nationality，这是一个像“41-33-11”varchar。 我有国家，城市和州表格，其中41个（“布法罗”），33个（“纽约”）和11个（“美国”）。
SELECT users.name，users.email，users.nationality FROM users
国籍=“41-33-11”
如何制作SELECT语句以获得“美国纽约州布法罗”？
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?
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
nationality = "41-33-11"
How do I craft a SELECT statement so to get "Buffalo, NY, USA"?
最满意答案
使用内联视图提取值，以便可以加入它们：
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_idUse 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
发布评论