SQL 多条件查询

 

        我们在做查询的时候，输入参数，查询结果。如果参数在1-5个 就要写32个IF语句。 IF的计算公式就是2的N次方 如果超过10个参数以上就是有1024以上个IF，这样写死的心都有了。

         但是我们也可以通过另一种很变通的方法来做到

        case when

select * from a

select * from a where name=name (name是 a表的字段)

这两个语句的查询结果是一样的

所以我们可以用以下的做法来做多条件查询

declare
@start_effdate varchar(20),
@end_effdate varchar(20),
@start_part varchar(20),
@end_part varchar(20),
@tr_site varchar(20),
@tr_trnbr varchar(20),
@tr_loc varchar(20),
@start_date varchar(20),
@end_date varchar(20),
@tr_serial varchar(20),
@ptp_buyer varchar(20)

set @start_effdate='2008-03-12'
set @end_effdate='2008-03-12'
set @start_part='A'
set @end_part='Z'
set @tr_site='2000,3000,5000'
set @tr_trnbr=''
set @tr_loc=''
set @start_date='2008-03-12'
set @end_date='2008-03-12'
set @tr_serial=''
set @ptp_buyer=''

declare @spilt varchar(100),
        @spilt1 varchar(100),
        @spilt2 varchar(100)
set @spilt=substring(@tr_site,1,4)
set @spilt1=substring(@tr_site,6,4)
set @spilt2=substring(@tr_site,11,4)

if(len(@start_part)<0 or @start_part='') and (len(@end_part)<0 or @end_part='')
begin
select * from dbo.invmis_last_view where tr_site in (@spilt,@spilt1,@spilt2)
                                    and tr_trnbr=case when len(@tr_trnbr)<0 or @tr_trnbr='' then tr_trnbr else @tr_trnbr end
                                    and tr_loc=case when len(@tr_loc)<0 or @tr_loc='' then tr_loc else @tr_loc end
                                    and tr_serial=case when len(@tr_serial)<0 or @tr_serial='' then tr_serial else @tr_serial end
                                    and ptp_buyer=case when len(@ptp_buyer)<0 or @ptp_buyer='' then ptp_buyer else @ptp_buyer end
                                    and tr_effdate between convert(varchar(10),convert(datetime,@start_effdate),120) and convert(varchar(10),convert(datetime,@end_effdate),120)
                                    and tr_date between convert(varchar(10),convert(datetime,@start_date),120) and convert(varchar(10),convert(datetime,@end_date),120)
end
else if(len(@start_part)>0 or @start_part<>'') and (len(@end_part)<0 or @end_part='')
begin
select * from dbo.invmis_last_view where tr_site in (@spilt,@spilt1,@spilt2)
                                    and tr_trnbr=case when len(@tr_trnbr)<0 or @tr_trnbr='' then tr_trnbr else @tr_trnbr end
                                    and tr_loc=case when len(@tr_loc)<0 or @tr_loc='' then tr_loc else @tr_loc end
                                    and tr_serial=case when len(@tr_serial)<0 or @tr_serial='' then tr_serial else @tr_serial end
                                    and ptp_buyer=case when len(@ptp_buyer)<0 or @ptp_buyer='' then ptp_buyer else @ptp_buyer end
                                    and tr_effdate between convert(varchar(10),convert(datetime,@start_effdate),120) and convert(varchar(10),convert(datetime,@end_effdate),120)
                                    and tr_date between convert(varchar(10),convert(datetime,@start_date),120) and convert(varchar(10),convert(datetime,@end_date),120)
                                    and tr_part like @start_part+'%'
end
else if(len(@start_part)<0 or @start_part='') and (len(@end_part)>0 or @end_part<>'')
begin
select * from dbo.invmis_last_view where tr_site in (@spilt,@spilt1,@spilt2)
                                    and tr_trnbr=case when len(@tr_trnbr)<0 or @tr_trnbr='' then tr_trnbr else @tr_trnbr end
                                    and tr_loc=case when len(@tr_loc)<0 or @tr_loc='' then tr_loc else @tr_loc end
                                    and tr_serial=case when len(@tr_serial)<0 or @tr_serial='' then tr_serial else @tr_serial end
                                    and ptp_buyer=case when len(@ptp_buyer)<0 or @ptp_buyer='' then ptp_buyer else @ptp_buyer end
                                    and tr_effdate between convert(varchar(10),convert(datetime,@start_effdate),120) and convert(varchar(10),convert(datetime,@end_effdate),120)
                                    and tr_date between convert(varchar(10),convert(datetime,@start_date),120) and convert(varchar(10),convert(datetime,@end_date),120)
                                    and tr_part like @end_part+'%'
end
else if(len(@start_part)>0 or @start_part<>'') and (len(@end_part)>0 or @end_part<>'')
begin
select * from dbo.invmis_last_view where tr_site in (@spilt,@spilt1,@spilt2)
                                    and tr_trnbr=case when len(@tr_trnbr)<0 or @tr_trnbr='' then tr_trnbr else @tr_trnbr end
                                    and tr_loc=case when len(@tr_loc)<0 or @tr_loc='' then tr_loc else @tr_loc end
                                    and tr_serial=case when len(@tr_serial)<0 or @tr_serial='' then tr_serial else @tr_serial end
                                    and ptp_buyer=case when len(@ptp_buyer)<0 or @ptp_buyer='' then ptp_buyer else @ptp_buyer end
                                   and tr_effdate between convert(varchar(10),convert(datetime,@start_effdate),120) and convert(varchar(10),convert(datetime,@end_effdate),120)
                                    and tr_date between convert(varchar(10),convert(datetime,@start_date),120) and convert(varchar(10),convert(datetime,@end_date),120)
                                    and tr_part between @start_part and @end_part
end