是否可以替换 SQL 查询中的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6909314/
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
Is it possible to replace values in SQL query?
提问by Antoine
I would like to know if it is possible in an SQL query to replace some values by something else, or if I need to do that in post-processing.
我想知道是否可以在 SQL 查询中用其他东西替换某些值，或者我是否需要在后处理中这样做。
Let me explain. I have this table:
让我解释。我有这张桌子：
|username| accepted |
|--------|----------|
| toto | NULL |
|--------|----------|
| foo | 0 |
|--------|----------|
| Rick | 1 |
|--------|----------|
| bar | 1 |
|--------|----------|
I want to know the numbers of row per value of accepted(nullable bit). I'm running this query:
我想知道accepted（可空位）的每个值的行数。我正在运行这个查询：
SELECT [accepted], count(*) FROM my_table GROUP BY [accepted]
Which should return:
哪个应该返回：
NULL 1
false 1
true 2
Is there some way to replace the acceptedvalues by more meaningful labels? Ideally I would like to have something like:
有没有办法accepted用更有意义的标签替换值？理想情况下，我想要类似的东西：
not_available 1
not_accepted 1
accepted 2
Is that feasible with SQL server 2008 R2?
这对 SQL Server 2008 R2 可行吗？
Thx.
谢谢。
回答by niktrs
If you have a few values:
如果你有几个值：
SELECT CASE [accepted]
WHEN 0 THEN 'not_accepted'
WHEN 1 THEN 'accepted '
ELSE 'not_available' END AS accepted
, count(*)
FROM my_table
GROUP BY CASE [accepted]
WHEN 0 THEN 'not_accepted'
WHEN 1 THEN 'accepted '
ELSE 'not_available' END
回答by gbn
Use a CASE
使用案例
SELECT
CASE [accepted]
WHEN 1 THEN 'accepted'
WHEN 0 THEN 'not_accepted'
ELSE 'not_available' --NULL
END AS [accepted], count(*)
FROM my_table
GROUP BY --try [accepted] by itself first
CASE [accepted]
WHEN 1 THEN 'accepted'
WHEN 0 THEN 'not_accepted'
ELSE 'not_available' --NULL
END
You may have to use the whole CASE in the GROUP BY. Unless you do this
您可能必须在 GROUP BY 中使用整个 CASE。除非你这样做
SELECT
CASE [accepted]
WHEN 1 THEN 'accepted'
WHEN 0 THEN 'not_accepted'
ELSE 'not_available' --NULL
END AS [accepted], CountOfAccepted
FROM
(SELECT [accepted], count(*) AS CountOfAccepted
FROM my_table GROUP BY [accepted]) foo
回答by potNPan
SELECT
CASE [accepted]
WHEN 1 THEN 'accepted',
WHEN 0 THEN 'not accepted',
ELSE 'not available'
END AS [accepted],
count(*)
FROM my_table GROUP BY [accepted]
?
?
Not sure if this is exactly right and don't have SQL Server available to test now, but should be something like that.
不确定这是否完全正确并且现在没有可用于测试的 SQL Server，但应该是这样的。
EDIT: aww, already posted
编辑：哇，已经发布
回答by pratik garg
yeah it is there ... you can modify your query as below -
是的，它在那里......你可以修改你的查询如下 -
SELECT decode(accepted,NULL,'not_available' ,1,'accepted',0,'not_accepted'),count(*)
FROM my_table GROUP BY
decode(accepted,NULL,'not_available' ,1,'accepted',0,'not_accepted')
it should give output as you want..
它应该根据需要提供输出..
may be decode function is not there then you can use caseover there..
可能是解码功能不存在然后你可以case在那里使用..
SELECT
case accepted
WHEN 1 THEN 'accepted'
WHEN 0 THEN 'not_accepted'
ELSE 'not_available'
end,count(*)
FROM my_table GROUP BY
case accepted
WHEN 1 THEN 'accepted'
WHEN 0 THEN 'not_accepted'
ELSE 'not_available' end
