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

原创 2006年11月02日 02:06:00

应用场景

 

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

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

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

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

原始查询

ALTER PROCEDURE [dbo].[QueryStudentHomework]
    
@teacherId int,
    
@state int--0:全部,1:已提交,2:已批改,3:已退回
    @queryType int,
    
@queryArg varchar(500)

AS
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)

AS
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 教学.学生作业.状态 = @stateINNER 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如何构建多条件组合查询,而且不降低效率

我们知道,在一般的信息系统中,特别是主要信息表,如客户基本信息,工单受理主界面,用户会用到多条件组合查询。QQ群请加: 6539042(powerbuilder11&SQL)  我看过一些系统,有的人...
  • chengg0769
  • chengg0769
  • 2007-12-12 14:10:00
  • 5631

SQL使用(一)-----联合查询

本文主要对内连接(inner Join 或 Join)、外连接(outer Join) 、左外连接(left outer Join 或 left Join)、右外连接(right outer Join...
  • u012999796
  • u012999796
  • 2017-03-15 17:40:45
  • 28821

SQL查询之组合查询

组合查询 SQL允许执行多个查询(多条SELECT语句),并将结果作为一个结果集返回.这些组合查询成为并(union)或复合查询(compound query) 有两种情况需要使用组合查询: 在一...
  • y874961524
  • y874961524
  • 2016-05-23 14:56:48
  • 6589

SQL语句聚合函数、分组、子查询及组合查询

聚合函数: SQL中提供的聚合函数可以用来统计、求和、求最值等等。 分类: –COUNT:统计行数量 –SUM:获取单个列的合计值 –AVG:计算某个列的平均值 –MAX:计算...
  • LiMing_0820
  • LiMing_0820
  • 2017-01-08 14:28:13
  • 1547

关系数据库SQL之高级数据查询:去重复、组合查询、连接查询、虚拟表

前言 接上一篇关系数据库SQL之基本数据查询:子查询、分组查询、模糊查询,主要是关系型数据库基本数据查询。包括子查询、分组查询、聚合函数查询、模糊查询,本文是介绍一下关系型数据库几种高级数据查询S...
  • SeayXu
  • SeayXu
  • 2016-06-03 17:32:08
  • 4746

SQL经典组合查询

从博客园中看到一篇文章,介绍大软件公司面试时常常会出的两道SQL题(见附录)。 我觉得受益很多,在此之前,我一直觉得,SQL2008似乎提供了这方面的支持,但更低的版本,包括2005,非游标做不出来(...
  • kissqi
  • kissqi
  • 2010-04-28 09:10:00
  • 15235

多条件组合查询

方法一://多条件组合查询 public List findMoreCondition(Customer customer) { //1-使用hibernate模板里面find...
  • pianpiannia
  • pianpiannia
  • 2017-08-21 09:27:10
  • 104

SQL-组合查询

如何利用UNION操作符将多条SELECT语句组合成一个结果集?SQL允许执行多个查询(多条SELECT语句),并将结果作为一个查询结果集返回。这些组合查询通常称为并(union)或复合查询(comp...
  • beauty_1991
  • beauty_1991
  • 2016-04-27 11:27:30
  • 2505

MyBatis中动态SQL语句完成多条件查询

一看这标题,我都感觉到是mybatis在动态SQL语句中的多条件查询是多么的强大,不仅让我们用SQL语句完成了对数据库的操作;还通过一些条件选择语句让我们SQL的多条件、动态查询更加容易、简洁、直观。...
  • yanggaosheng
  • yanggaosheng
  • 2015-06-29 19:00:07
  • 34142

实现多条件模糊查询SQL语句

很多网友问到如何写模糊查询语句和多条件查询,这里我整理了一下,假设以姓名、性别、电话号...作为数据库中的字段名。 通常写一个简单的模糊查询的SQL语句格式可以如下例:  ...
  • mjh11310
  • mjh11310
  • 2007-06-18 09:53:00
  • 26380
收藏助手
不良信息举报
您举报文章:多条件组合查询SQL优化一则
举报原因:
原因补充:

(最多只允许输入30个字)