declare @a int select @a=1 select 1 where (case when @a>=1 then 1 else 0 end)+ (case when @a>=2 then 1 else 0 end)+(case when @a>=3 then 1 else 0 end)+ (case when @a>=4 then 1 else 0 end)+(case when @a=5 then 1 else 0 end)>=3
select * from 表名 where (case when 条件1 then 1 else 0 end+ case when 条件2 then 1 else 0 end+ case when 条件3 then 1 else 0 end+ case when 条件4 then 1 else 0 end+ case when 条件5 then 1 else 0 end)>=3
--或者使用nullif和isnull组合,如isnull(nullif(字段,值),1)declare @t table(id int,col varchar(10)) insert into @t select 1,'a'select * from @t where isnull(nullif(id,1),1)+isnull(nullif(col,'a'),1)+isnull(nullif(1,1),1)+isnull(nullif(2,3),1)+isnull(nullif(id,3),1)>=3
不能算,nullif的限制性比较强
select * from 表名 where (case when 条件1 then 1 else 0 end+ case when 条件2 then 1 else 0 end+ case when 条件3 then 1 else 0 end+ case when 条件4 then 1 else 0 end+ case when 条件5 then 1 else 0 end)>=3 )强
为什么不这样呢 select * form table where a=.. and b=.. and c=.. unoin select * form table where a=.. and b=.. and d=.. unoin select * form table where a=.. and c=.. and d=.. unoin ...... select ....
以前的老问题.多个union的性能是很差的。
而且 5选3的组合,你要写多少个union,呵呵
这个不错select * from 表名 where (case when 条件1 then 1 else 0 end+ case when 条件2 then 1 else 0 end+ case when 条件3 then 1 else 0 end+ case when 条件4 then 1 else 0 end+ case when 条件5 then 1 else 0 end)>=3 看看再说
学习:select * from 表名 where (case when 条件1 then 1 else 0 end+ case when 条件2 then 1 else 0 end+ case when 条件3 then 1 else 0 end+ case when 条件4 then 1 else 0 end+ case when 条件5 then 1 else 0 end)>=3
select * from 表名 where (case when 条件1 then 1 else 0 end+ case when 条件2 then 1 else 0 end+ case when 条件3 then 1 else 0 end+ case when 条件4 then 1 else 0 end+ case when 条件5 then 1 else 0 end)>=3This way is very good!
和楼上的兄弟大同小异用decode select * from table where ( decode(条件1,1,0)+ decode(条件2,1,0)+ decode(条件3,1,0)+ decode(条件4,1,0)+ decode(条件5,1,0) )>= 3
select * from table1 where 1=1 字段名1=case when 1>1 then 1 else 字段名1 end 字段名2=case when 1>2 then 2 else 字段名2 end 字段名3=case when 1>3 then 3 else 字段名3 end 字段名4=case when 1>4 then 4 else 字段名4 end 字段名5=case when 1>5 then 5 else 字段名5 end
select * from table where (条件1 and 条件2 and 条件3) or (条件1 and 条件2 and 条件4) or (条件1 and 条件2 and 条件5) or (条件1 and 条件3 and 条件4) or (条件1 and 条件3 and 条件4) or (条件1 and 条件4 and 条件5) or (条件2 and 条件3 and 条件4) or (条件2 and 条件3 and 条件5) or (条件2 and 条件4 and 条件5) or (条件3 and 条件4 and 条件5)
select @a=1
select 1 where (case when @a>=1 then 1 else 0 end)+
(case when @a>=2 then 1 else 0 end)+(case when @a>=3 then 1 else 0 end)+
(case when @a>=4 then 1 else 0 end)+(case when @a=5 then 1 else 0 end)>=3
select * from 表名
where
(case when 条件1 then 1 else 0 end+
case when 条件2 then 1 else 0 end+
case when 条件3 then 1 else 0 end+
case when 条件4 then 1 else 0 end+
case when 条件5 then 1 else 0 end)>=3
不知道还有没有更好的方法,用一条sql语句来写!
insert into @t select 1,'a'select * from @t
where isnull(nullif(id,1),1)+isnull(nullif(col,'a'),1)+isnull(nullif(1,1),1)+isnull(nullif(2,3),1)+isnull(nullif(id,3),1)>=3
where
(case when 条件1 then 1 else 0 end+
case when 条件2 then 1 else 0 end+
case when 条件3 then 1 else 0 end+
case when 条件4 then 1 else 0 end+
case when 条件5 then 1 else 0 end)>=3
)强
select * form table where a=.. and b=.. and c=..
unoin
select * form table where a=.. and b=.. and d=..
unoin
select * form table where a=.. and c=.. and d=..
unoin
......
select ....
where
(case when 条件1 then 1 else 0 end+
case when 条件2 then 1 else 0 end+
case when 条件3 then 1 else 0 end+
case when 条件4 then 1 else 0 end+
case when 条件5 then 1 else 0 end)>=3
看看再说
where
(case when 条件1 then 1 else 0 end+
case when 条件2 then 1 else 0 end+
case when 条件3 then 1 else 0 end+
case when 条件4 then 1 else 0 end+
case when 条件5 then 1 else 0 end)>=3
where
(case when 条件1 then 1 else 0 end+
case when 条件2 then 1 else 0 end+
case when 条件3 then 1 else 0 end+
case when 条件4 then 1 else 0 end+
case when 条件5 then 1 else 0 end)>=3This way is very good!
select * from table where
(
decode(条件1,1,0)+
decode(条件2,1,0)+
decode(条件3,1,0)+
decode(条件4,1,0)+
decode(条件5,1,0)
)>= 3
where 1=1
字段名1=case when 1>1 then 1 else 字段名1 end
字段名2=case when 1>2 then 2 else 字段名2 end
字段名3=case when 1>3 then 3 else 字段名3 end
字段名4=case when 1>4 then 4 else 字段名4 end
字段名5=case when 1>5 then 5 else 字段名5 end
根据上下文也许可以提取共通部分调整SQL语句的写法。
毫不搭界的任意条件,没办法!
where
(条件1 and 条件2 and 条件3)
or (条件1 and 条件2 and 条件4)
or (条件1 and 条件2 and 条件5)
or (条件1 and 条件3 and 条件4)
or (条件1 and 条件3 and 条件4)
or (条件1 and 条件4 and 条件5)
or (条件2 and 条件3 and 条件4)
or (条件2 and 条件3 and 条件5)
or (条件2 and 条件4 and 条件5)
or (条件3 and 条件4 and 条件5)