现在需要执行一个查询语句,从两个表中查询。有A、B、C三种输入条件,每次都接收,但是不一定都有输入值,不同的组合查询出不同的结果(有输入条件的情况),比如说:
1.A有值,B、C没有
select * from TableA as A inner join TableB as B
on A.Id = B.Id AND ( B.Type = 1 )
2.A、B有值,C没有
select * from TableA as A inner join TableB as B
on A.Id = B.Id AND ( B.Type = 1 AND B.Type = 2 )
................
但是如果要把所有情况遍历的话,得用多个if语句(上层是vc 用ADO执行):A、B、C、AB、AC、BC、ABC 如果有4个或者更多条件,组合就更多!!!
怎么才能用一个语句或者少量的语句完成操作?
1.A有值,B、C没有
select * from TableA as A inner join TableB as B
on A.Id = B.Id AND ( B.Type = 1 )
2.A、B有值,C没有
select * from TableA as A inner join TableB as B
on A.Id = B.Id AND ( B.Type = 1 AND B.Type = 2 )
................
但是如果要把所有情况遍历的话,得用多个if语句(上层是vc 用ADO执行):A、B、C、AB、AC、BC、ABC 如果有4个或者更多条件,组合就更多!!!
怎么才能用一个语句或者少量的语句完成操作?
select * from TableA as A inner join TableB as B
on A.Id = B.Id
where
B.Type=(case
when @A is null or @A='' then B.type else @A end)
and
B.Type=(case
when @B is null or @B='' then B.type else @B end)
AND
B.Type=(case
when @C is null or @C='' then B.type else @C end)
--我在想你的语句
select * from TableA as A inner join TableB as B
on A.Id = B.Id AND ( B.Type = 1 AND B.Type = 2 )
能查出来什么数据?
Type 就是像1,2,3这样的数据吧,那怎么还可以B.Type即等于1有等于2???建议你给出测试数据
declare @a varchar(1000), --条件a
@b varchar(1000), --条件b
@c varchar(1000) --条件c
set @str='select * from TableA as A inner join TableB as B
on A.Id = B.Id 'if (@a<>'')
set @str=@str+' and ('+@a+')'if (@b<>'')
set @str=@str+' and ('+@b+')'if (@c<>'')
set @str=@str+' and ('+@c+')'exec (@str)最好就是写在一个存储过程里直接调用。
对于多条件可传可不传的情况, 可以考虑用 Or 来做, 写法上类似这样
select * from TableA as A inner join TableB as B
on A.Id = B.Id AND (B.Column1 = @A or @A is null) AND (B.Column2 = @B or @B is null) AND (B.Column3 = @C or @C is null)