比如我有一句查询语句如下SELECT [code]
,[type]
FROM [Item]查询结果如下
code type
001 1
002 1
005 2
014 3
100 2我怎么把查询结果中的type 下,1=S 2=Z 3=O 的替换掉,成下面这样
code type
001 S
002 S
005 Z
014 O
100 Z
,[type]
FROM [Item]查询结果如下
code type
001 1
002 1
005 2
014 3
100 2我怎么把查询结果中的type 下,1=S 2=Z 3=O 的替换掉,成下面这样
code type
001 S
002 S
005 Z
014 O
100 Z
[code],
case [type] when 1 then 'S' when 2 then 'Z' when 3 then 'O' else [type] end as [type]
from [item]
if object_id('[item]') is not null drop table [item]
go
create table [item]([code] varchar(3),[type] varchar(2))
insert [item]
select '001','1' union all
select '002','1' union all
select '005','2' union all
select '014','3' union all
select '100','2'
---查询---
select
[code],
case [type] when 1 then 'S' when 2 then 'Z' when 3 then 'O' else [type] end as [type]
from [item]---结果---
code type
---- ----
001 S
002 S
005 Z
014 O
100 Z(所影响的行数为 5 行)
from item
insert tb
select '001','1' union all
select '002','1' union all
select '005','2' union all
select '014','3' union all
select '100','2'select code , case when type = 1 then 'S' when type = 2 then 'Z' when type = 3 then 'O' end type from tbdrop table tb/*
code type
---- ----
001 S
002 S
005 Z
014 O
100 Z(所影响的行数为 5 行)
*/
insert into @tb select 001,'1'
union all select 002,'1'
union all select 005,'2'
union all select 014,'3'
union all select 100,'2'
select code,TYPE= case when type=1 then 's' when type=2 then 'z'
else '0' end from @tb
code TYPE
----------- ----
1 s
2 s
5 z
14 0
100 z(5 行受影响)
,(case [type] when 1 then 'S' when 2 then 'Z' else 'O' end) as [type] FROM [Item] [/code]
,(case
when type=1 then 'S' when type=2 then 'Z' when type=3 then 'O' end )
)as [type]
FROM [Item]