select case when 字段='true' the 1 else 0 end from tb
select case when 字段='true' then 1 else 0 end as [新字段名]
select case value when 'true' then 1 when 'false' then 0 end
不明白楼主的意思,在MSSQL里,逻辑型数据是由bit类型来处理的,true 就是1,false 就是0. create table tb(col bit) insert into tb select 1 union all select 0 go select * from tb /* col ----- 1 0(2 行受影响)*/ go drop table tb
create table #tb(bool bit) insert #tb select 'true' union all select 'false' union all select 'false' union all select 'true' select case bool when 'true' then 1 else 0 end as bool from #tb
select case value when 1 then 'true' when 0 then 'false' else 'unknown' end from ...
create table Table01 (字段01 bit,字段02 bit,字段03 bit) insert Table01 select 'true', 'false', 'true' union all select 'false', 'false', 'true' declare @sql nvarchar(4000) set @sql='' select @sql=@sql+' union all select Rowid,'''+[name]+''' as ColnumName,' +'case when '+[name]+'=''true'' then 1 else 0 end as [value] from T' from sys.columns where [object_id]= object_id('Table01') set @sql=';with T as (select Row_number()over(order by getdate()) as Rowid,* from Table01)'+ stuff(@sql,1,10,'') exec(@sql)--Rowid ColnumName value ---------------------- ---------- ----------- --1 字段01 1 --2 字段01 0 --1 字段02 0 --2 字段02 0 --1 字段03 1 --2 字段03 1 -- --(6 row(s) affected)
select case value
when 'true' then 1
when 'false' then 0 end
create table tb(col bit)
insert into tb select 1 union all select 0
go
select * from tb
/*
col
-----
1
0(2 行受影响)*/
go
drop table tb
create table #tb(bool bit)
insert #tb
select 'true' union all
select 'false' union all
select 'false' union all
select 'true' select case bool when 'true' then 1 else 0 end as bool from #tb
true, false, true怎么返回以下的报表:
字段01 1
字段02 0
字段03 1
case value
when 1 then 'true'
when 0 then 'false'
else 'unknown'
end
from ...
create table Table01
(字段01 bit,字段02 bit,字段03 bit)
insert Table01
select 'true', 'false', 'true' union all
select 'false', 'false', 'true' declare @sql nvarchar(4000)
set @sql=''
select @sql=@sql+' union all select Rowid,'''+[name]+''' as ColnumName,'
+'case when '+[name]+'=''true'' then 1 else 0 end as [value] from T' from sys.columns
where [object_id]= object_id('Table01')
set @sql=';with T as (select Row_number()over(order by getdate()) as Rowid,* from Table01)'+
stuff(@sql,1,10,'')
exec(@sql)--Rowid ColnumName value
---------------------- ---------- -----------
--1 字段01 1
--2 字段01 0
--1 字段02 0
--2 字段02 0
--1 字段03 1
--2 字段03 1
--
--(6 row(s) affected)