MySQL案例向后处理
MySQL案例向后处理
提问于 2012-10-16 03:59:01
我正在使用CASE清理表格中的一些状态缩写，但它的工作原理与逻辑相反。我单独选择了长度，以表明长度计算正确，所以我认为案例逻辑是不正确的
当我查询时...
SELECT billing_state,
length(billing_state),
CASE billing_state
WHEN length(billing_state) > 2 THEN (select state_abbr from lkup_states where upper(state_name) = billing_state)
WHEN length(billing_state) = 2 THEN upper(billing_state)
ELSE 'UNKNOWN'
END as billing_state_fixed
FROM accounts
+---------------+-----------------------+---------------------+
| billing_state | length(billing_state) | billing_state_fixed |
+---------------+-----------------------+---------------------+
| GA | 2 | NULL |
| Alabama | 7 | ALABAMA |
| MS | 2 | NULL |
| FL | 2 | NULL |
| NULL | NULL | UNKNOWN |
+---------------+-----------------------+---------------------+然而，当我进入这个奇怪的逻辑时，它就起作用了。
SELECT billing_state,
length(billing_state),
CASE billing_state
WHEN length(billing_state) = 2 THEN (select state_abbr from lkup_states where upper(state_name) = billing_state)
WHEN length(billing_state) <> 2 THEN upper(billing_state)
ELSE 'UNKNOWN'
END as billing_state_fixed
FROM accounts
+---------------+-----------------------+---------------------+
| billing_state | length(billing_state) | billing_state_fixed |
+---------------+-----------------------+---------------------+
| GA | 2 | GA |
| Alabama | 7 | AL |
| MS | 2 | MS |
| FL | 2 | FL |
| NULL | NULL | UNKNOWN |
+---------------+-----------------------+---------------------+有没有人能试一试这个？
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-10-16 04:04:04
根据the docs的说法，您的语法不太正确。
你把CASE value WHEN compare_value和CASE WHEN expression搞混了。
你可能想要的是：
SELECT billing_state,
length(billing_state),
CASE
WHEN length(billing_state) > 2 THEN (select state_abbr from lkup_states where upper(state_name) = billing_state)
WHEN length(billing_state) = 2 THEN upper(billing_state)
ELSE 'UNKNOWN'
END as billing_state_fixed
FROM accounts页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12903080
复制相关文章
相似问题
腾讯云开发者
