至顶网›软件频道 ›基础软件›多条件组合查询SQL优化一则

多条件组合查询SQL优化一则

扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条

将特定的查询条件从WHERE子句移至FROM子句的ON条件里

作者：土豆爸爸来源：CSDN 2007年12月28日

关键字：优化 SQL 组合查询多条件

应用场景

上图中要求按以下条件查询学生作业：

1、按学生作业的状态：全部 / 待批改 / 已批改

2、按学生学号 / 学生姓名 / 作业题目 / 全部

其中1和2是“并”的关系。

原始查询

ALTER PROCEDURE [dbo].[QueryStudentHomework]

@teacherId int,

@state int, --0:全部,1:已提交,2:已批改,3:已退回

@queryType int,

@queryArg varchar(500)

BEGIN

SET NOCOUNT ON;

DECLARE @queryIntArg int, @queryStringArg varchar(502)

IF(@queryType = 1)

SET @queryIntArg = Convert(int, @queryArg);

ELSE IF(@queryType = 2 OR @queryType = 3)

SET @queryStringArg = '%' + @queryArg + '%'

SELECT 教学.学生作业.学生作业Id as '@id',

教学.课程.课程简称 + ' : ' + 教学.课程班.课程班名称 AS '@classname',

教学.作业.作业题目 as '@title',

教学.作业.发布时间 as '@publishTime',

教学.课程作业.上交时间 as '@commitTime',

教学.作业.作业类型 as '@type',

教学.学生作业.状态 as '@state',

教学.作业.作业内容 as 'content',

org.学生.学号 as 'commit/@studentCode',

org.学生.姓名 as 'commit/@studentName',

教学.学生作业.提交时间 as 'commit/@commitTime',

教学.学生作业.答案附件Id as 'commit/@fileId',

pub.Upload.FileName as 'commit/@fileName',

教学.学生作业.作业答案 as 'commit',

教学.学生作业.批改人 as 'check/@checker',

教学.学生作业.批改时间 as 'check/@checkTime',

教学.学生作业.分数 as 'check/@score',

教学.学生作业.评语 as 'check'

FROM 教学.课程 INNER JOIN

教学.课程班 ON 教学.课程班.课程Id = 教学.课程.课程Id INNER JOIN

教学.课程作业 ON 教学.课程作业.课程班Id = 教学.课程班.课程班Id INNER JOIN

教学.作业 ON 教学.作业.作业Id = 教学.课程作业.作业Id INNER JOIN

教学.学生作业 ON 教学.学生作业.作业Id = 教学.课程作业.作业Id AND 教学.学生作业.课程班Id = 教学.课程班.课程班Id INNER JOIN

org.学生 ON org.学生.学生Id = 教学.学生作业.学生Id INNER JOIN

教学.课程班权限 ON 教学.课程班权限.课程班Id = 教学.课程班.课程班Id AND 教学.课程班权限.作业批改 = 1 LEFT OUTER JOIN

pub.Upload ON pub.Upload.FileId = 教学.学生作业.答案附件Id

WHERE 教学.课程班权限.教师Id = @teacherId

AND (教学.课程班.状态 = 1)

AND (@state = 0 OR 教学.学生作业.状态 = @state)

AND (@queryType = 0

OR (@queryType = 1 AND 教学.学生作业.学生Id = @queryIntArg)

OR (@queryType = 2 AND org.学生.姓名 like @queryStringArg)

OR (@queryType = 3 AND 教学.课程班.课程班Id = @queryIntArg)

)

ORDER BY 提交时间

FOR XML PATH('homework'), ROOT('homeworks')

END

执行时间20几秒。

优化查询

ALTER PROCEDURE [dbo].[QueryStudentHomework]

@teacherId int,

@state int, --0:全部,1:已提交,2:已批改,3:已退回

@queryType int,

@queryArg varchar(500)

BEGIN

SET NOCOUNT ON;

DECLARE @queryIntArg int, @queryStringArg varchar(502)

IF(@queryType = 1)

SET @queryIntArg = Convert(int, @queryArg);

ELSE IF(@queryType = 2 OR @queryType = 3)

SET @queryStringArg = '%' + @queryArg + '%'

SELECT 教学.学生作业.学生作业Id as '@id',

教学.课程.课程简称 + ' : ' + 教学.课程班.课程班名称 AS '@classname',

教学.作业.作业题目 as '@title',

教学.作业.发布时间 as '@publishTime',

教学.课程作业.上交时间 as '@commitTime',

教学.作业.作业类型 as '@type',

教学.学生作业.状态 as '@state',

教学.作业.作业内容 as 'content',

org.学生.学号 as 'commit/@studentCode',

org.学生.姓名 as 'commit/@studentName',

教学.学生作业.提交时间 as 'commit/@commitTime',

教学.学生作业.答案附件Id as 'commit/@fileId',

pub.Upload.FileName as 'commit/@fileName',

教学.学生作业.作业答案 as 'commit',

教学.学生作业.批改人 as 'check/@checker',

教学.学生作业.批改时间 as 'check/@checkTime',

教学.学生作业.分数 as 'check/@score',

教学.学生作业.评语 as 'check'

FROM 教学.课程 INNER JOIN

教学.课程班 ON 教学.课程班.课程Id = 教学.课程.课程Id AND 教学.课程班.状态 = 1 INNER JOIN

教学.课程作业 ON 教学.课程作业.课程班Id = 教学.课程班.课程班Id INNER JOIN

教学.作业 ON 教学.作业.作业Id = 教学.课程作业.作业Id INNER JOIN

教学.学生作业 ON 教学.学生作业.作业Id = 教学.课程作业.作业Id

AND 教学.学生作业.课程班Id = 教学.课程班.课程班Id

AND (@state = 0 OR 教学.学生作业.状态 = @state) INNER JOIN

org.学生 ON org.学生.学生Id = 教学.学生作业.学生Id INNER JOIN

教学.课程班权限 ON 教学.课程班权限.课程班Id = 教学.课程班.课程班Id

AND 教学.课程班权限.作业批改 = 1

AND 教学.课程班权限.教师Id = @teacherId LEFT OUTER JOIN

pub.Upload ON pub.Upload.FileId = 教学.学生作业.答案附件Id

WHERE @queryType = 0

OR (@queryType = 1 AND 教学.学生作业.学生Id = @queryIntArg)

OR (@queryType = 2 AND org.学生.姓名 like @queryStringArg)

OR (@queryType = 3 AND 教学.作业.作业题目 like @queryStringArg)

ORDER BY 提交时间

FOR XML PATH('homework'), ROOT('homeworks')

END

将特定的查询条件从WHERE子句移至FROM子句的ON条件里，执行时间为0.04秒。

查看本文来源

多条件组合查询SQL优化一则

VIP专区

VIP用户

普通用户

邮件订阅

如果您非常迫切的想了解IT领域最新产品与技术信息，那么订阅至顶网技术邮件将是您的最佳途径之一。

重磅专题

往期文章

多条件组合查询SQL优化一则

应用场景

原始查询

优化查询

业界热点: