WHERE 子句中的多个过滤器
1.63/5 (8投票s)
2005年5月2日
6分钟阅读
52145
如何在 SQL 语句的 WHERE 子句中编写多个过滤器。
WHERE 子句中的多个过滤器
引言
有时我需要开发一个报表或一些显示数据的东西，但之前用户可以应用多个筛选器，以便查看想要查找的信息。
我一如既往地设计了带有筛选器的用户界面，然后将筛选器传递到不同的层，最后，它们到达了一个存储过程，该存储过程进行查询。
它返回数据，仅此而已。
我曾多次听到一些同事说，他们必须显示应用了多个筛选器的数据，但最终在存储过程中，他们除了通过构建字符串然后使用 EXEC sql 命令执行它，或者制作多个 SELECT 语句之外，找不到其他方法来构建查询。
示例
以 pubs 数据库为例说明这种情况。
我们必须显示属于某个出版商或拥有特定职位的员工。
用户界面如下所示
您可以通过选择职位或（所有职位）来筛选职位，也可以通过选择出版商或（所有出版商）来筛选出版商。
解决查询的存储过程大致如下所示
制作多个 SELECT 语句
CREATE PROCEDURE dbo.GetEmployeesFiltered1
(
@JobFilter int ,
@PublisherFilter int
)
AS
begin
declare @FilterByJob bit
declare @FilterByPub bit
if @JobFilter > 0
begin
select @FilterByJob = 1
end
else
begin
select @FilterByJob = 0
end
if @PublisherFilter > 0
begin
select @FilterByPub = 1
end
else
begin
select @FilterByPub = 0
end
if @FilterByJob=1
BEGIN
if @FilterByPub=1
BEGIN
--按职位和出版商筛选
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id
WHERE
publishers.pub_id = @PublisherFilter
或者
jobs.job_id = @JobFilter
END
else
--按职位筛选
BEGIN
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id
WHERE
jobs.job_id = @JobFilter
END
END
else
--不按职位筛选
BEGIN
if @FilterByPub=1
BEGIN
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id
WHERE
publishers.pub_id = @PublisherFilter
END
else
--不按出版商筛选
BEGIN
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id
END
END
end
这类过程的问题在于维护起来很麻烦，您必须记住为所有选项/筛选器维护 SELECT 语句。
所以可以用这种方式解决
通过 EXEC 命令执行构建的 SELECT 语句
CREATE PROCEDURE dbo.GetEmployeesFiltered2
(
@JobFilter int ,
@PublisherFilter int
)
AS
begin
declare @FilterByJob bit
declare @FilterByPub bit
if @JobFilter > 0
begin
select @FilterByJob = 1
end
else
begin
select @FilterByJob = 0
end
if @PublisherFilter > 0
begin
select @FilterByPub = 1
end
else
begin
select @FilterByPub = 0
end
declare @mySentece varchar (1000)
declare @myFilter varchar (1000)
select @mySentece = 'SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id'
if @FilterByJob=1
BEGIN
if @FilterByPub=1
BEGIN
--按职位和出版商筛选
SELECT @myFilter = 'publishers.pub_id = ' + @PublisherFilter + '
OR
jobs.job_id = ' + @JobFilter
END
else
--按职位筛选
BEGIN
SELECT @myFilter = 'jobs.job_id = ' + @JobFilter
END
END
else
--不按职位筛选
BEGIN
if @FilterByPub=1
BEGIN
SELECT @myFilter = 'publishers.pub_id = ' + @PublisherFilter
END
else
--不按出版商筛选
BEGIN
SELECT @myFilter = ''
END
END
end
if @myFilter = ''
begin
exec ( @mySentece )
end
else
begin
exec ( @mySentece + ' WHERE ' + @myFilter )
end
这里解决了为所有选项/筛选器维护 SELECT 语句的问题，但它存在 SQL Server 必须在每次执行时编译存储过程的问题。
多重筛选 SELECT 语句
这就是我提出的带有 多个筛选器的查询解决方案
CREATE PROCEDURE dbo.GetEmployeesFiltered3
(
@JobFilter int ,
@PublisherFilter int
)
AS
begin
declare @FilterByJob bit
declare @FilterByPub bit
if @JobFilter > 0
begin
select @FilterByJob = 1
end
else
begin
select @FilterByJob = 0
end
if @PublisherFilter > 0
begin
select @FilterByPub = 1
end
else
begin
select @FilterByPub = 0
end
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id
WHERE
(@FilterByPub=1 AND publishers.pub_id = @PublisherFilter )
或者
(@FilterByJob=1 AND jobs.job_id = @JobFilter )
End
主要步骤是确定要“激活”哪些筛选器。当此指示器打开时，它将应用 WHERE 搜索条件。
这是在 WHERE 子句中构建多个筛选器的诀窍。
关于 fhunth
最近，我受雇于 Huddle Group (http://www.huddle.com.ar/) 担任 C# 高级开发人员
联系我：
访问我的博客：
http://msdevelopers.blogspot.com/