table tab1
column1 column2 column3
select * from tab1 where (column1 between x1 and y1 ) and (column2 between x2 and y2 ) and (column3 between x3 and y3 )
大致思路是这样,但是如果x1和y1只要有一个为假,就去掉(column1 between x1 and y1 )
x2和y2只要有一个为假,就去掉(column2 between x2 and y2 )
x3和y3只要有一个为假,就去掉(column3 between x3 and y3 )
column1 column2 column3
select * from tab1 where (column1 between x1 and y1 ) and (column2 between x2 and y2 ) and (column3 between x3 and y3 )
大致思路是这样,但是如果x1和y1只要有一个为假,就去掉(column1 between x1 and y1 )
x2和y2只要有一个为假,就去掉(column2 between x2 and y2 )
x3和y3只要有一个为假,就去掉(column3 between x3 and y3 )
table tab1
column1 column2 column3
即是x1为假
y1 = "" or y1 is null
即是y1为假
AS
BEGIN
DELCARE @WHERE VARCHAR(8000)
SET @WHERE=''
IF 条件1 成立SET @WHERE=@WHERE+' AND (column1 between x1 and y1)';IF 条件2 成立
SET @WHERE=@WHERE+' AND (column2 between x2 and y2 )';IF 条件3 成立
SET @WHERE=@WHERE+' AND (column3 between x3 and y3 )';SET @WHERE='SELECT * FROM TB WHERE 1=1'+@WHEREEXEC(@WHERE)END
set @i=5
set @j=null
select number from master..spt_values
where type='p' and ((number between @i and @j) or (@i is null) or (@j is null))
--等效于
select number from master..spt_values
where type='p'
--也就是相当于去掉了对number的判断,同理select * from tab1
where ((column1 between x1 and y1) or(x1 is null) or(y1 is null))
and ((column2 between x2 and y2) or(x2 is null) or(y2 is null))
and ((column3 between x3 and y3) or(x3 is null) or(y3 is null))
没理解错的话,对于column1 between x1 and y1
x1或者y1为null则舍弃对column1的判断