第11章 高级检索技术

第11章高级检索技术教学目标●理解和掌握聚合技术●理解和掌握分组技术，特别是ROLLUP和CUBE●理解和掌握连接技术●理解和掌握子查询技术●理解和掌握集合运算技术●理解和掌握CTE技术教学难点和重点●理解和掌握聚合技术，这是数据统计的基础。

理解和掌握分组技术，特别是ROLLUP和CUBE，这是提高数据统计效率的关键和难点。

理解和掌握连接技术，这是把多个表中数据连接在一个结果集中的基本手段。

理解和掌握子查询技术，这也是检索表中数据的最基本和最重要的手段之一。

理解和掌握集合运算技术，这是综合运用多个查询语句的基础。

理解和掌握CTE技术，这是实现递归、完成复杂操作的有效方式。

教学过程●聚合技术●分组技术●连接技术●子查询技术●集合运算技术●公用表表达式●上机实验11.1 聚合技术●聚合技术是指对一组数据进行聚合运算得到聚合值的过程。

在聚合运算中主要是使用聚合函数。

在Microsoft SQL Server 2005系统中，一般情况下，可以在3个地方使用聚合函数，即SELECT子句、COMPUTE子句和HAVING子句。

本节主要讲述如何在SELECT子句和COMPUTE子句中使用聚合函数，有关HAVING子句使用聚合函数的内容将在下一节介绍。

SELECT子句中的聚合●在SELECT子句中可以使用聚合函数进行运算，运算结果作为新列出现在结果集中。

在聚合运算的表达式中，可以包括列名、常量以及由算术运算符连接起来的函数。

COMPUTE子句中的聚合●COMPUTE子句使用聚合函数计算聚合值，并且可以依然保持原有的明细值，新的聚合值作为特殊的列出现。

COMPUTE 子句有两种形式，一种形式是不带BY子句，另一种形式是带BY子句。

COMPUTE子句中如果没有包含BY子句，表示对所有的明细值计算聚合值；如果包含了BY子句，则表示按照BY子句的要求对明细值分组，然后给出每一组的聚合值。

11.2 分组技术●聚合函数只能产生一个单一的汇总数据，使用GROUP BY子句，则可以生成分组的汇总数据。

GROUP BY子句把数据组织起来分成组。

一般情况下，可以根据表中的某一列进行分组，通过使用聚合函数，对每一个组可以产生聚合值。

如果希望过滤某些分组，可以使用HAVING子句。

●分组技术是指使用GROUP BY子句完成分组操作的技术。

如果在GROUP BY子句中没有使用CUBE或ROLLUP关键字，表示这种分组技术是普通分组技术。

普通分组技术●GROUP BY子句、HAVING子句和聚合函数一起完成对每一个组生成一行和一个汇总值。

●在SELECT子句中的非合计列必须出现在GROUP BY子句中。

●在HAVING子句中的列只返回一个值。

ROLLUP和CUBE关键字●在GROUP BY子句中，可以使用ROLLUP或CUBE关键字获得附加的分组数据，这些附加的分组数据是通过各组之间的组合得到的。

使用ROLLUP关键字可以得到各组的单项组合，而CUBE关键字可以得到各组之间的任意组合。

●在结果集中，通过组组合起来的组名称是NULL，可以使用GROUPING函数来判断该组是否为经过组合得到的。

实际上，使用CUBE关键字可以生成多维数据。

9.3 连接技术●实现从两个或两个以上表中检索数据且结果集中出现的列来自于两个或两个以上表中的检索操作被称为连接技术，或者说连接技术是指对两个或两个以上表中数据执行乘积运算的技术。

在设计表时，为了提高表的设计质量，经常把相关数据分散在不同的表中。

但是，在使用数据时，需要把这些数据集中在一个查询语句中。

连接技术可以满足这种客观需求。

●在Microsoft SQL Server 2005系统中，这种连接操作又可以细分为交叉连接、内连接、外连接等。

下面分别介绍这些连接技术。

交叉连接●交叉连接也被称为笛卡尔乘积，返回两个表的乘积。

在检索结果集中，包含了所连接的两个表中所有行的全部组合。

例如，如果对A表和B表执行交叉连接，A表中有5行数据，B 表中有12行数据，则结果集中可以有60行数据。

●交叉连接使用CROSS JOIN关键字来创建。

内连接●内连接把两个表中的数据连接生成第三个表，在这第三个表中，仅包含那些满足连接条件的数据行。

●在内连接中，使用INNER JOIN连接运算符，并且使用ON关键字指定连接条件。

内连接是一种常用的连接方式，如果在JOIN关键字前面没有明确指定连接类型，则默认的连接类型是内连接。

外连接●内连接是保证两个表中所有的行都要满足连接条件，但是外连接则不然。

在外连接中，不仅仅是那些满足条件的数据，某些不满足条件的数据也会显示在结果集中。

也就是说，外连接只限制其中一个表的数据行，而不限制另外一个表中的数据。

●在Microsoft SQL Server 2005系统中，可以使用3种外连接关键字，即LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN。

9.4 子查询技术●SELECT语句可以嵌套在许多其他语句中，这些语句包括SELECT、INSERT、UPDATE和DELETE等，这些嵌套的SELECT语句被称为子查询。

当一个查询依赖于另外一个查询结果时，就可以使用子查询。

在某些查询中，查询语句比较复杂，不容易理解，因此，当为了把这些复杂的查询语句分解成多个比较简单的查询语句形式时，可以使用子查询方式。

使用子查询方式完成查询操作的技术是子查询技术。

9.5 集合运算技术●查询语句的结果集往往是一个包含了多行数据的集合。

集合之间可以进行并、差、交等运算。

在Microsoft SQL Server 2005系统中，两个查询语句之间也可以进行集合运算。

其中，UNION运算符表示并集运算，EXCEPT运算符从左查询中返回右查询中没有找到的重复值，INTERSECT运算符则返回左右两个查询语句都包含的所有非重复值。

●需要注意的是，在集合运算时，所有查询语句中的列的数量和顺序必须相同，且数据类型必须兼容。

9.6 公用表表达式●在Microsoft SQL Server 2005系统中，可以使用公用表表达式(common table expression，CTE)。

CTE是定义在SELECT、INSERT、UPDATE或DELETE语句中的临时命名的结果集，CTE也可以用在视图的定义中。

●在CTE中，可以包含对自身的引用，因此这种表达式也被称为递归CTE。

9.7 上机实验●练习使用分组和聚合技术●练习使用连接和子查询技术综合练习--构造级别表create table guid(id int,--编号a int,--低边界值b int,dep varchar(20)--高边界值)insert guidselect 1,0,60,'不及格'union allselect 2,60,70,'中等'union allselect 3,70,80,'良好'union allselect 4,80,100,'优秀'select*from guidcreate table score(no int,score decimal(5,2)) insert scoreselect 1,100union all select 2,90union all select 3,89union all select 4,23union all select 5,57union all select 6,78union all select 7,99select等级=g.dep,人数=count(学号),百分比=CASE WHEN Counts=0 THEN'0.00%'ELSE CAST(CAST(COUNT(成绩)*100./c.Countsas decimal(10,2))as varchar)+'%' ENDfrom guid g left join scon sc.成绩>=g.a and sc.成绩<=g.bcross join(select counts=count(*)from sc) c group by g.dep,c.counts2有表格和表格其中id编号，department 部门dptid 部门编号name 员工姓名设计查询，结果如下：--创建第一个表格create table testtable1(id int IDENTITY,department varchar(12))--插入数据insert into testtable1 values('设计')insert into testtable1 values('市场')insert into testtable1 values('售后')--查看select*from testtable1--创建第二个表格create table testtable2(id int IDENTITY,dptID int,name varchar(12))--插入数据insert into testtable2 values(1,'张三')insert into testtable2 values(1,'李四')insert into testtable2 values(2,'王五')insert into testtable2 values(3,'彭六')insert into testtable2 values(4,'陈七')--查看信息select*from testtable2--设计的查询selecta.id,dptID,department=isnull(department,'黑人'),[name]from testtable2 a left join testtable1 bon b.id=a.dptid3有表格完成下列查询其中：p_id产品编号，p_num 产品库存s_id 仓库号S1_id 1仓库，s2_id 2仓库s3_id 3仓库。

--设计的查询select p_id ,sum(case when s_id=1 then p_num else 0 end)as s1_id,sum(case when s_id=2 then p_num else0 end)as s2_id,sum(case when s_id=3 then p_num else0 end)as s3_idfrom test1 group by p_id--4. 从jxgl(教学管理数据库)中表sc中给学生成绩排名，成绩相同的名次相同。