sql多值查询

[zychenzy]

我的查询有4个条件，每个条件又是可有可无的（每次查询可以全选，也可以不选条件），我以前是用IF　then 来写的，但是现在条件多了，过多的IF语句会引响查询速度，请问大侠们怎么做，
最好写语句看一下。

[zychenzy]

写一个让我看一下，先谢了。

[hsj20041004]

还是用IF　then 比较好的。
这样好判断，代码也清晰，可读性强。

[mengzulin]

select * from table
where (id=@id or @id=null) and (name = @name or @name=null) and (age =@age or age<0)

[seu31199113]

要具体看你是什么条件了！
给一个例子，帮你分析分析

[WangZWang]

declare @str varchar(8000)
Set @Str='select * from tbl where id=0 '
if ---
set @str=@str+' and ???'
----------- 之类的,这样组成的语句在效率上就有所改观
exec(@str)

[zychenzy]

A　B　C　D四个条件。
if A="" and B<>""AND C<>"" ADN D<>""then sql=......
IF b="" AND ......
这样的话就会有好多种组合，好像是16种，那执行效果不会很好吧，
大侠们怎样处理这种问题的！

[zychenzy]

大侠们帮个忙，谢过大家了！

[WangZWang]

用动态语句实现,灵活.

[qgqchile]

WangZWang(阿来) 的方法 很不错啊,我就是这样做多样查询的,给你个现成的STORE PROCEDURE做参考
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Pos_ListarPos'
AND type = 'P')
DROP PROCEDURE Pos_ListarPos
GO

CREATE PROCEDURE Pos_ListarPos
@StrXMLDatos VARCHAR(8000) AS

DECLARE @idoc INT


DECLARE @pais NUMERIC
DECLARE @ciudad Numeric


DECLARE @sexo NUMERIC
DECLARE @estadoCivil NUMERIC

DECLARE @licencia NUMERIC
DECLARE @titulo numeric


DECLARE @contador numeric

DECLARE @edad numeric


DECLARE @strSelect VARCHAR(1000)

DECLARE @strFrom VARCHAR(1000)

DECLARE @strWhere VARCHAR(4500)

DECLARE @strOrder VARCHAR(500)

DECLARE @strInsert VARCHAR(1000)

DECLARE @strSQL VARCHAR(8000)

BEGIN TRANSACTION TX

EXEC sp_xml_preparedocument @idoc OUTPUT, @StrXMLDatos


-- Lee documento XML de entrada
SELECT

@pais=pais,
@ciudad=ciudad,


@sexo=sexo,
@estadoCivil=estadoCivil,

@licencia=licencia,
@titulo=titulo,
@edad=edad


FROM OPENXML (@idoc, '/parametros',2)
WITH (

pais NUMERIC,
ciudad numeric,

sexo NUMERIC,
estadoCivil NUMERIC,
nacimiento VARCHAR(10),

licencia NUMERIC,
titulo numeric,
edad numeric
)

EXEC sp_xml_removedocument @idoc



BEGIN



SET @strSelect = ''
SET @strFrom = ''

SET @strSQL = ''
SET @strSelect=''

SET @strWhere=' where id_pos<> '''+ ''''

SET @strSelect ='SELECT a.* '
SET @strFrom = ' from PosI_Ficha a '

if @pais<>0
set @strFrom=@strFrom+' ,admin.dbo.detCodSys b '
set @strWhere =@strWhere+' and pais= '+ cast(@pais as varchar)
+' and '+ cast(@pais as varchar) + ' =b.idCodDet '

--SET @strSQL = @strSelect + @strFrom + @strWhere
--print @strSQL
--print @ciudad

if @ciudad<>0
set @strSelect=@strSelect+' ,c.descCodDet as ciudad '

if @ciudad<>0
set @strFrom=@strFrom+' ,admin.dbo.detCodSys c '

if @ciudad<>0
set @strWhere=@strWhere+' and ciudad= '+cast(@ciudad as varchar)
+' and '+ cast(@ciudad as varchar) + ' =c.idCodDet '


if @sexo<>0
set @strSelect=@strSelect+' ,d.descCodDet as sexo '
set @strFrom=@strFrom+' ,admin.dbo.detCodSys d '
set @strWhere=@strWhere+' and sexo= '+cast(@sexo as varchar)
+' and '+ cast(@sexo as varchar) + ' =d.idCodDet '



if @estadoCivil<>0
set @strSelect=@strSelect+' ,e.descCodDet as estadoCivil '
set @strFrom=@strFrom+' ,admin.dbo.detCodSys e '
set @strWhere=@strWhere+' and estadoCivil= '+cast(@estadoCivil as varchar)
+' and '+ cast(@estadoCivil as varchar) + ' =e.idCodDet '



if @licencia<>0
set @strSelect=@strSelect+' ,g.descCodDet as licencia '
set @strFrom=@strFrom+' ,admin.dbo.detCodSys g '
set @strWhere=@strWhere+' and licencia = '+ cast(@licencia as varchar) +' and '+
cast(@licencia as varchar)+ ' = g.idCodDet'



if @titulo<>0
set @strWhere=@strWhere+' and titulo= '+ cast(@titulo as varchar)

if @edad<>0
set @strWhere=@strWhere+' and DATEDIFF("m",convert(varchar,nacimiento,103),convert (varchar,getdate(),103))/12<= '
+ cast(@edad as varchar)

SET @strSQL = @strSelect + @strFrom + @strWhere

--SELECT a.* ,c.descCodDet as ciudad from PosI_Ficha a ,admin.dbo.detCodSys b ,admin.dbo.detCodSys c where id_pos<> '' and pais= 53 and 53 =b.idCodDet and ciudad= 56 and 56 =c.idCodDet
--SELECT * from PosI_Ficha where id_pos<> '' and DATEDIFF("m",convert(varchar,nacimiento,103),convert (varchar,getdate(),103))/12= 0
END
--print @strSQL
EXEC (@strSQL)

COMMIT TRANSACTION TX