sql case语句中的动态运算符

问题描述

如何在case语句中使用operator，请参阅下面的示例。

How I use operator in case statement,please see bellow example.

CreatedByClient =
case @AddedBy
when 0 then  CreatedByClient
when 1 then 0
when 2 then >0
end

我根据@AddedBy写一个案例，我试着当@AddedBy值为2时，CreatedByClient> 0我该怎么做。请帮助。







您好，感谢所有回复，请看下面

I write a case based on @AddedBy ,I am try to when @AddedBy value is 2 then CreatedByClient>0 how do I do this.please help.



Hi ,thanks to all for reply,please see bellow

987
458
0
45
0
0
0
58
569
2

for查询我传递0,1,2，0表示返回全部，1表示只返回0而2表示只返回非0行。



请帮我怎么做这样做。

for query I pass 0,1,2,for 0 return all,for 1 return only 0 and for 2 return only non 0 rows.

please assists me how do I do this .

推荐答案

--setup test data
declare @TabNum table(id int identity(1,1), num int);
insert into @TabNum
	select 987 num
	union all select 458
	union all select 0
	union all select 45
	union all select 0
	union all select 0
	union all select 0
	union all select 58
	union all select 569
	union all select 2
;

--test @AddedBy with different values 0, 1 and 2
declare @AddedBy integer;
set @AddedBy = 0;
--set @AddedBy = 1;
--set @AddedBy = 2;

select * from @TabNum
--for query I pass 0,1,2,
where (@AddedBy <> 0 or num >= 0) --for 0 return all,
and (@AddedBy <> 1 or num = 0) --for 1 return only 0 and 
and (@AddedBy <> 2 or num <> 0) --for 2 return only non 0 rows.
;

这篇关于sql case语句中的动态运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！