使用SELECT时，可以基于其他字段修改返回字段的值吗? [英] When using SELECT can you modify the value of a returned field based on other fields?

问题描述

使用MySQL SELECT时，可以基于其他字段更改返回字段的值吗?

When using MySQL SELECT can you change the value of a returned field based on other fields?

例如，如果我有此选择:

For example, if I have this select:

SELECT city,state,country FROM table

现在，如果城市为空且州为空，我希望country的值也返回空(country实际是否有值).

Now if city is empty AND state is empty, I want the value of country to returned empty as well (whether country actually has a value or not).

示例表:

id | city | state | country
-----------------------------
1  | Here | There | MyCountry
2  |      |       | YourCountry

所以在上表中，我希望id = 1的结果返回Here，There，MyCountry，但是id = 2的结果应该为空，空，空

So with the above table, I want the results for id=1 to return Here,There,MyCountry but the results for id=2 should be empty,empty,empty

谢谢

编辑:为了澄清，WHERE子句将不起作用，因为即使城市和州为空，我也需要返回该行.更好的示例可能是my_table的SELECT id，city，state，country

To clarify, a WHERE clause will not work because I need the row returned even if city and state are empty. A better example might have been SELECT id,city,state,country FROM my_table

推荐答案

更新(打印错误已纠正):

Update(misprints corrected):

SELECT city,state,
CASE 
 WHEN (city IS NULL OR city='') AND (state IS NULL or state='') THEN ''
 ELSE country
END as country_1
 FROM `table`

您也可以使用IF代替CASE:
IF ((city IS NULL OR city='') AND (state IS NULL or state=''),'',country) as country_1

You can also use IF instead of CASE:
IF ((city IS NULL OR city='') AND (state IS NULL or state=''),'',country) as country_1

这篇关于使用SELECT时，可以基于其他字段修改返回字段的值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！