我是想查询一个字段下面所有的值 比如
table a
-----------------------------------------------
b
1 1
2 1
3 1
4 0
-----------------------------------------------
如果B列值都为1,返回Y,如果还有别的值,返回X
table a
-----------------------------------------------
b
1 1
2 1
3 1
4 0
-----------------------------------------------
如果B列值都为1,返回Y,如果还有别的值,返回X
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,0
select b=case when exists(select 1 from @t where b<>1) then 'X' else 'Y' endb
----
X(所影响的行数为 1 行)
--新建一个函数判断列中是否都是'1'
if object_id('FN_ckStr')>0
drop function FN_ckStr
gocreate function FN_ckStr(@str varchar(8000))
returns int
begin
declare @i int
declare @re int
set @str = replace(@str,' ','')
set @i = 1
set @re = 1 while @i<len(@str)
begin
if substring(@str,@i,1) <> '1'
begin
set @re = 0
break
end
set @i = @i + 1
end return @re
enddeclare @a table(b varchar(30))
insert into @a
select '1 1' union all
select '2 1' union all
select '3 1' union all
select '4 0' select case dbo.FN_ckStr(b) when 1 then 'Y' else 'N' end as b
from @a
from @a
(所影响的行数为 4 行)b
----
Y
X
X
X(所影响的行数为 4 行)
我调试下,少候结帖。
select X
else select Y
select b=case when exists(select b from @t where b<>1) then 'X' else 'Y' end
此处是1而不是b
select b=case when exists(select b from @t where b<>1) then 'X' else 'Y' end
此处是b而不是1SORRY:刚才写错了