问题描述
我有一张表格,其中有一列具有不同的值,例如美国、韩国、日本等.我想用 America=USA, South Korea=SA, Japan= JP 替换这些值.代码是什么?
I have a table where there is a column with different values like America, South Korea, Japan and so on. I would like to replace the values with America=USA, South Korea=SA, Japan= JP like these. What would be the code?
推荐答案
可能处理此问题的最佳方法是维护一个单独的表,将完整的国家/地区名称映射到它们的两个字母代码:
The best way to probably handle this would be to maintain a separate table which maps full country names to their two letter codes:
country_full | country_abbr
America | USA
South Korea | SA
Japan | JP
然后,您可以将您当前的表加入到这个查找表中以引入代码:
Then, you may join your current table to this lookup table to bring in the codes:
SELECT
t1.*,
t2.country_abbr
FROM yourTable t1
LEFT JOIN country_codes t2
ON t1.country = t2.country_full;
另一种处理这个问题的方法,虽然不是很可扩展,但是使用 CASE
表达式来引入代码:
Another way to handle this, though not very scalable, would be to use a CASE
expression to bring in the codes:
SELECT
country,
CASE country WHEN 'America' THEN 'USA'
WHEN 'South Korea' THEN 'SA'
WHEN 'Japan' THEN 'JP'
ELSE 'Unknown' END As code
FROM yourTable;
这篇关于更改 bigquery 中的列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!