如何在SQL Server中打印与列中的数字相对应的文本值 [英] How to print Text values corresponding to numbers in a column in sql server

问题描述

要求如下:
我有一个名为tblactivity的表，其中有一个列如checkin_flag.
此checkin_flag列可以包含值[0,1,2,4,6].
现在相应的文本值如下:
0->不完整
1->完成
2->已发布
4->已发布
6->已取消
现在我需要编写一个查询，在该查询中将打印对应于其Text值的checkin_flag值.没有其他表可以保持此数字值->文本值的关系.
您能否让我知道如何编写查询:
从tblactivity中选择checkedin_flag作为CheckedIn_Status，但是我想显示文本值.连接查询以获取值.

如果您不能或不想使用这种方法，则需要使用一个case语句

  SELECT 
    '  CheckedIn_Status' =
    案例 Checkin_flag
        何时  0  > THEN  不完整'
        何时  1  > THEN  完整'
        ...
         ELSE  ' 未知值' - 引用完整性只是应使用FK表的原因之一
     END 
 FROM 
    活动性
订单  BY 
    checkin_flag; 

如果使用这种方法，我建议您将其设置为函数-速度会较慢，但只需要将其维护在一个位置即可

尝试一下:

 选择 大小写 checkin_flag 何时  0  然后 ' 不完整" 
何时  1  然后 完整'
何时  2  然后 已发布'
何时  4  >然后 已发布'
何时  6  >然后 已取消'
其他 ' -'
结束 CheckedIn_Status 来自 tblactivity 

希望对您有帮助.

个人而言，我将创建第二个表来执行此操作-它更具可维护性.
但是，如果您做不到:

  SELECT  *， CASE  Checkin_flag
            何时  0  > THEN  不完整'
            何时  1  > THEN  完整'
            何时  2  > THEN  已发布'
            何时  4  > THEN  已发布'
            何时  6  > THEN  已取消'
             ELSE  ' 未知'
           END 
    FROM  myTable 

Hi,
The requirement is follows:
I have a table called tblactivity,where I have a column as checkedin_flag.
This checkedin_flag column can contain values [0,1,2,4,6].
Now the corresponding text values are as follows:
0->Incomplete
1->Complete
2->Posted
4->Posted
6->Cancelled
Now I need to write a query where the checkedin_flag value will be printed corresponding to their Text value.There is no other table where this number value->Text value relation is maintained.
Can you please let me know how to write the query:
select checkedin_flag as CheckedIn_Status from tblactivity --but I want to display the text values.

解决方案

I would strongly suggest that you create a foreign key table to maintain this and use a join query to get the value.

If you can''t or don''t want to use this approach then you would need to use a case statement

SELECT 
    'CheckedIn_Status' =
    CASE checkedin_flag
        WHEN 0 THEN 'Incomplete'
        WHEN 1 THEN 'Complete'
        ...
        ELSE 'Unknown Value' -- Referential Integrity is just one reason why a FK table should be used
    END
FROM 
    tblactivity
ORDER BY 
    checkedin_flag;

If you use this approach I would suggest that you make it a function - it would be slower but you would only need to maintain it in one place

Try this :

select case checkedin_flag when 0 then 'Incomplete' 
when 1 then 'Complete' 
when 2 then 'Posted' 
when 4 then 'Posted' 
when 6 then 'Cancelled' 
else '-'
end CheckedIn_Status from tblactivity

Hope It Helps.

Personally, I would create a second table to do this - it is a lot more maintainable.
But, if you can''t:

SELECT *, CASE checkedin_flag 
            WHEN 0 THEN 'Incomplete'
            WHEN 1 THEN 'Complete'
            WHEN 2 THEN 'Posted'
            WHEN 4 THEN 'Posted'
            WHEN 6 THEN 'Cancelled'
            ELSE 'Unknown'
          END
   FROM myTable

这篇关于如何在SQL Server中打印与列中的数字相对应的文本值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！