如何联接表并将数字邮政编码值替换为文本邮政编码值 [英] How to join a table and replace a numeric zip code value with a text zip code value

问题描述

我有一个表 main_cohort ，该表有两个不同的列，其中包含3位和5位数字的邮政编码(例如100或10017)， zip_value3 和 zip_value5 ，我想将其与另一个具有相同列的表 zip_codes 以及具有文本字符串的附加列 city_name 联接起来每个邮递区号所对应的城市的位置.

I have one table main_cohort that has two different columns which contain zip codes in 3 digit and 5 digit numeric form (e.g. 100 or 10017), zip_value3 and zip_value5, and I want to join it with another table zip_codes that has these same columns as well as an additional column, city_name, that has the text string of the city associated with each zip.

我想用第二个表上的城市名称替换第一个表上的数字值.因此，举例来说，如果我在"main_cohort"中有一个"zip_value3"为100的customer_id，我想用 zip_code 表，该表将是纽约.

I want to replace the numeric values on the first table with the names of the cities on the second table. So for instance, if I have a customer_id in 'main_cohort' that has a 'zip_value3' of 100, I want to replace that in my output with the value from the city_name from the zip_code table, which would be New York.

推荐答案

我想您要一个城市，首先检查5位数的邮政编码，然后再检查3位数的邮政编码:

I am guessing that you want one city, first checking the 5-digit zip code and then the 3-digit one:

select mc.*, coalesce(zc5.city_name, zc3.city_name) as city_name
from main_cohort mc left join
     zip_code zc5
     on mc.zip_value5 = zc5.zip_code left join
     zip_code zc3
     on mc.zip_value3 = zc3.zip_code

这篇关于如何联接表并将数字邮政编码值替换为文本邮政编码值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！