至顶网›软件频道 ›基础软件›多条件组合查询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用户

普通用户

闂侇収鍠曞▎銏㈡媼閵忋倖顫�

濠碘€冲€归悘澶愬箖閵娾晜濮滈悽顖涚摃閹烩晠宕氶崶鈺傜暠闁诡垰鍘栫花锛勬喆椤ゅ弧濡澘妫楅悡娆撳嫉閳ь剟寮０渚€鐛撻柛婵呮缁楀矂骞庨埀顒勫嫉椤栨瑤绻嗛柟顓у灲缁辨繈鏌囬敐鍕杽閻犱降鍨藉Σ鍕嚊閹跺鈧﹦绱旈幋鐐参楅柡鍫灦閸嬫牗绂掔捄铏规闁哄嫷鍨遍崑宥夋儍閸曨剚浠樺ù锝嗗▕閳ь剚鏌ㄧ欢鐐寸▕鐎ｂ晝顏遍柕鍡嫹

重磅专题

往期文章

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

应用场景

原始查询

优化查询

业界热点: