MySQL SQL 如何替换选择返回的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16753122/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL How to replace values of select return?
提问by Lai32290
In my database (MySQL) table, has a column with 1and 0for represent trueand falserespectively.
在我的数据库（MySQL的）表中，有一列1，并0为代表true和false分别。
But in SELECT, I need it replace for trueor falsefor printing in a GridView.
但是SELECT，在 GridView中，我需要它替换true或false打印。
How to I make my SELECTquery to do this?
如何进行SELECT查询以执行此操作？
In my current table:
在我当前的表中：
id | name | hide
1 | Paul | 1
2 | John | 0
3 | Jessica | 1
I need it show thereby:
我需要它显示：
id | name | hide
1 | Paul | true
2 | John | false
3 | Jessica | true
回答by frugal-one
You have a number of choices:
您有多种选择：
- Join with a domain table with
TRUE,FALSEBooleanvalue. Use (as pointed in this answer)
SELECT CASE WHEN hide = 0 THEN FALSE ELSE TRUE END FROMOr if Booleanis not supported:
SELECT CASE WHEN hide = 0 THEN 'false' ELSE 'true' END FROM
- 与具有
TRUE,FALSE布尔值的域表连接。 使用（如本答案中所指出的）
SELECT CASE WHEN hide = 0 THEN FALSE ELSE TRUE END FROM或者，如果不支持布尔值：
SELECT CASE WHEN hide = 0 THEN 'false' ELSE 'true' END FROM
回答by Amilcar Andrade
You can do something like this:
你可以这样做：
SELECT id,name, REPLACE(REPLACE(hide,0,"false"),1,"true") AS hide FROM your-table
Hope this can help you.
希望这可以帮到你。
回答by Sandeep Kamath
I got the solution
我得到了解决方案
SELECT
CASE status
WHEN 'VS' THEN 'validated by subsidiary'
WHEN 'NA' THEN 'not acceptable'
WHEN 'D' THEN 'delisted'
ELSE 'validated'
END AS STATUS
FROM SUPP_STATUS
This is using the CASE This is another to manipulate the selected value for more that two options.
这是使用 CASE 这是另一个操作选择的值以获得两个以上的选项。
回答by Jonathan Leffler
If you want the column as string values, then:
如果您希望该列作为字符串值，则：
SELECT id, name, CASE WHEN hide = 0 THEN 'false' ELSE 'true' END AS hide
FROM anonymous_table
If the DBMS supports BOOLEAN, you can use instead:
如果 DBMS 支持 BOOLEAN，则可以使用：
SELECT id, name, CASE WHEN hide = 0 THEN false ELSE true END AS hide
FROM anonymous_table
That's the same except that the quotes around the names falseand truewere removed.
除了名称周围的引号false和true被删除之外，这是相同的。
回答by jospratik
You can use casting in the select clause like:
您可以在 select 子句中使用强制转换，例如：
SELECT id, name, CAST(hide AS BOOLEAN) FROM table_name;
回答by A.D.
I saying that the case statement is wrong but this can be a good solution instead.
If you choose to use the CASEstatement, you have to make sure that at least one of the CASEcondition is matched. Otherwise, you need to define an error handler to catch the error. Recall that you don't have to do this with the IFstatement.
我说 case 语句是错误的，但这可能是一个很好的解决方案。如果您选择使用该CASE语句，则必须确保至少有一个CASE条件匹配。否则，您需要定义一个错误处理程序来捕获错误。回想一下，您不必对IF语句执行此操作。
SELECT if(hide = 0,FALSE,TRUE) col FROM tbl; #for BOOLEAN Value return
or
或者
SELECT if(hide = 0,'FALSE','TRUE') col FROM tbl; #for string Value return
回答by Abu Abdullah
Replace the value in select statement itself...
替换 select 语句本身中的值...
(CASE WHEN Mobile LIKE '966%' THEN (select REPLACE(CAST(Mobile AS nvarchar(MAX)),'966','0')) ELSE Mobile END)
(CASE WHEN Mobile LIKE '966%' THEN (select REPLACE(CAST(Mobile AS nvarchar(MAX)),'966','0')) ELSE Mobile END)
