是否可以替换SQL查询中的值？

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:

我想知道接受的每个值的行数(可空位)。我正在运行此查询:

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 accepted values by more meaningful labels? Ideally I would like to have something like:

有没有办法用更有意义的标签替换接受的值?理想情况下,我希望有类似的东西:

not_available 1
not_accepted  1
accepted      2

Is that feasible with SQL server 2008 R2?

这对SQL Server 2008 R2是否可行?

Thx.

4 个解决方案

#1

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

#2

Use a CASE

使用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

#3

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

编辑:aww,已发布

#4

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 case over there..

可能是解码功能不存在然后你可以使用案例那边..

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

#1