当前位置: 萬仟网 > IT编程>数据库>MSSQL > SQL Server温故系列(4):SQL 查询之集合运算 & 聚合函数

SQL Server温故系列(4):SQL 查询之集合运算 & 聚合函数

2019年07月09日 17:43  | 萬仟网IT编程  | 我要评论

1、集合运算

在数学中,不仅可以对指定的数字个体做四则运算,还可以对指定的集合整体做交并补运算。类似的,在数据库中也是不仅可以对具体的数据行进行增删改查,还可以对查询结果集进行集合运算。sql server 中的集合运算有并集运算、差集运算和交集运算三种,本节将逐一讲述。

1.1、并集运算 union

并集运算符 union 的作用是将两个或多个查询的结果集合并为单个结果集。在 union 运算中,需要确保各个子结果集的字段数相同、字段的顺序相同、字段的数据类型兼容。合并结果集的列名始终会取第一个子结果集中的列名,因此,如果要对合并结果集排序,则需要用第一个子结果集的字段名。

参数 all 表示将全部行并入结果集中,换句话说合并结果集可能会包含重复行;相反,如果未指定该参数,则会删除重复行。需要注意的是,在做重复判断时,union 会把两个 null 值被视为相等的。因为 union all 不需要删除重复行,所以性能比 union 要好。因此,除非必须要删除重复行,否则建议一律使用 union all。

1.1.1、简单 union all。查询 1 班的男生和 2 班的女生。示例如下:

select * from t_students t1 where t1.classid = 1 and t1.gender = 1
union all
select * from t_students t2 where t2.classid = 2 and t2.gender = 0;

1.1.2、简单 union all。查询 1 班的男生和该班的男生人数。示例如下:

select 1 cnt,t1.code,t1.name from t_students t1 where t1.classid = 1 and t1.gender = 1
union all
select count(1),null,null from t_students t2 where t2.classid = 1 and t2.gender = 1;

查询结果如下:

cnt         code                           name
----------- ------------------------------ ------------------------------
1           s330102001                     郑强
1           s330102002                     肖俊生
1           s330300007                     钱波
1           s330104009                     金桥
4           null                           null

1.1.3、union 与 order by。查询 1 班的男生和 2 班的女生,并且将最终结果集按年龄从大到小排序。示例如下:

select * from t_students t1 where t1.classid = 1 and t1.gender = 1
union all
select * from t_students t2 where t2.classid = 2 and t2.gender = 0
order by t1.birthday;

1.1.4、union 与 select into。创建一个活动学生表,并将 1 班的男生和 2 班的女生加入其中。示例如下:

select t1.code,t1.name,t1.gender,t1.birthday 
into t_activitystudents 
from t_students t1 
where t1.classid = 1 and t1.gender=1 
union all
select t2.code,t2.name,t2.gender,t2.birthday 
from t_students t2 
where t2.classid = 2 and t2.gender=0;

1.1.5、通过括号来改变 union 的运算顺序。示例如下:

with t as(
    select top(3) * from t_students t where t.classid = 1 order by t.birthday
)
select * from t
union all(
    select * from t
    union
    select * from t
);

简单说明一下:首先,上例会返回 6 条数据。其次,很明显 cte 中包含了年龄最大的 3 个学生。然后,括号会提升运算符的优先级,而且 union 会删除重复行,所以括号中会返回 3 条数据。最后,union all 不会删除重复行,所以最终返回的 6 条数据是第一个查询返回的 3 条数据加上括号中两个查询共同产生的 3 条数据。

1.2、差集运算 except

差集运算符 except 的作用是比较两个查询的结果集,然后返回左侧结果集包含但右侧结果集不包含的行,且不包含重复行。与 union 运算相同,except 运算也要求各个子结果集的字段数相同、顺序相同、类型兼容。且 except 也会把两个 null 值被视为相等的。示例如下:

select * from t_goodstudents t1 where t1.gender = 0
except
select * from t_goodstudents t2 where t2.birthday < '2000-01-01';

注意:上例是用女生这个结果集减去非 00 后学生结果集,但得到的结果集并不一定是 00 后女生,因为女生的出生日期可能是 null,所以最终的结果集是 00 后女生加上出生日期为 null 的女生。

类似于 union:except 也可以与 order by 连用来给差集(最终的结果集)排序,而且差集的列名也跟第一个子结果集的列名相同。except 还可以与 select into 连用将差集拷贝到一个新表中。当有多个 except 时也可以通过括号来改变运算顺序。具体用法可参考 union 的示例。

1.3、交集运算 intersect

交集运算符 intersect 的作用是比较两个查询的结果集,然后返回左侧结果集和右侧结果集都包含的行,且不包含重复行。与 union 运算相同,intersect 运算也要求各个子结果集的字段数相同、顺序相同、类型兼容。且 intersect 也会把两个 null 值被视为相等的。示例如下:

select * from t_goodstudents t1 where t1.gender = 0
intersect
select * from t_goodstudents t2 where t2.birthday < '2000-01-01';

注意:上例是取女生这个结果集和非 00 后学生结果集的交集,但得到的结果集并不一定是非 00 后女生,因为女生的出生日期可能是 null,所以最终的结果集是非 00 后女生加上出生日期为 null 的女生。

类似于 union:intersect 也可以与 order by 连用来给交集(最终的结果集)排序,而且交集的列名也跟第一个子结果集的列名相同。intersect 还可以与 select into 连用将交集拷贝到一个新表中。当有多个 intersect 时也可以通过括号来改变运算顺序。具体用法可参考 union 的示例。

1.4、集合运算小结

上文逐一讲述了各个集合运算符的语法和用途。其实这些集合运算符不仅可以单独使用,还可以结合起来使用,示例如下:

select t1.id,t1.name,t1.gender,t1.birthday from t_students t1 where t1.classid = 1
union all
select t2.id,t2.name,t2.gender,t2.birthday from t_students t2 where t2.classid = 3
except
select * from t_goodstudents t3 where t3.birthday < '2000-01-01'
intersect
select * from t_goodstudents t4 where t4.gender = 1;

注意:上例的运算顺序并不是先 union all,然后 except,再 intersect。因为 intersect 比 except 和 union 的优先级要高,而 except 与 union 的优先级相同,所以上例的实际运算顺序是先 intersect,然后 union all,最后再用 union all 的结果集减 intersect 的结果集,即最后进行 except 运算。

2、聚合函数

聚合函数的作用是对一组值执行计算,并返回单个结果值。聚合函数只能在 select 子句或 having 子句中作为表达式来用。求行数函数 count、求和函数 sum、求最大值函数 max、求最小值函数 min、求平均值函数 avg,这 5 个函数是最常用的聚合函数,主流的关系型数据库也都支持它们。

其实常见的关系型数据库都支持很多聚合函数,但其中大部分都是非标准的,各个数据库之间的差别也比较大,而且这些函数也都不常用。个人建议实际工作中不要用这些函数,以免跟某个具体的数据库绑死。

2.1、求行数函数 count

求行数函数 count 会返回查询结果集的行数。count 函数中可以是一个具体的列,也可以是代表所有列的星号,还可以是一个具体的常量或变量。示例如下:

select count(t.id) result from t_students t; -- result:32 rows
select count(*) result from t_students t;    -- result:32 rows
select count(1) result from t_students t;    -- result:32 rows

count 是所有聚合函数中唯一不会忽略 null 值的函数,但如果被计算列本身含有 null 值是会被忽略的。示例如下:

select count(remark) result from t_students t; -- result:28 rows

在 count 函数中添加 distinct 参数,表示会先去除重复行,然后统计剩下的非 null 且唯一的值的个数。示例如下:

select count(distinct remark) result from t_students t; -- result:26 rows

注意:count 函数的返回值一定是大于或等于 0 的整数,证明如下:

select count(*) result;             -- result:1 rows
select count(*) result where 1 = 2; -- result:0 rows

另外,从 sql server 2008 开始,官方在增加了一个count_big函数,它的用法和用途与 count 完全相同,唯一不同的就是返回值的类型。count 函数总是返回一个 int 类型的整数,而count_big函数总是返回一个 bigint 类型的整数。示例如下:

select count_big(1) result from t_students t;               -- result:32 rows
select count_big(remark) result from t_students t;          -- result:28 rows
select count_big(distinct remark) result from t_students t; -- result:26 rows

2.2、求和函数 sum

求和函数 sum 会返回表达式中所有值的和。sum 函数会忽略所有 null 值,且只能应用于数字类型的字段。例如要查询学生 1 第 1 次考试的总分,示例如下:

select sum(t.scores) from t_examresults t where t.studentid = 1 and t.counts = 1;

在 sum 函数中添加 distinct 参数,表示会先去除重复行,然后统计剩下的非 null 且唯一的数值之和。示例如下:

select sum(t.scores) result from t_examresults t where t.studentid = 6;          -- result:2202.5
select sum(distinct t.scores) result from t_examresults t where t.studentid = 6; -- result:1857.5

注意:sum 函数的返回值有可能会是 null 值,证明如下:

select sum(1) result;             -- result:1
select sum(1) result where 1 = 2; -- result:null

2.3、求最大值函数 max

求最大值函数 max 会返回表达式中的最大值。max 函数会忽略所有 null 值。例如要查询学生 1 课程 1 的历次考试最高分,示例如下:

select max(t.scores) from t_examresults t where t.studentid = 1 and t.counts = 1;

max 函数还可以作用于日期类型或字符类型,此时 max 将按照日期数值或字符排序顺序来确定最大值。示例如下:

select max(t.birthday) from t_students t; -- 数值最大的出生日期,年龄最小
select max(t.name) from t_students t;     -- 字符排序最靠后的姓名

注意:max 函数的返回值有可能会是 null 值,证明如下:

select max(1) result;             -- result:1
select max(1) result where 1 = 2; -- result:null

2.4、求最小值函数 min

求最小值函数 min 会返回表达式中的最小值。min 函数会忽略所有 null 值。例如要查询学生 1 课程 1 的历次考试最低分,示例如下:

select min(t.scores) from t_examresults t where t.studentid = 1 and t.counts = 1;

min 函数还可以作用于日期类型或字符类型,此时 max 将按照日期数值或字符排序顺序来确定最小值。示例如下:

select min(t.birthday) from t_students t; -- 数值最小的出生日期,年龄最大
select min(t.name) from t_students t;     -- 字符排序最靠前的姓名

注意:min 函数的返回值有可能会是 null 值,证明如下:

select min(1) result;             -- result:1
select min(1) result where 1 = 2; -- result:null

2.5、求平均值函数 avg

求平均值函数 avg 会返回表达式中所有值的平均值。avg 函数会忽略所有 null 值,且只能应用于数字类型的字段。例如要查询学生 1 第 1 次考试的平均分,示例如下:

select avg(t.scores) from t_examresults t where t.studentid = 1 and t.counts = 1;

在 avg 函数中添加 distinct 参数,表示会先去除重复行,然后统计剩下的非 null 且唯一的数值之和。示例如下:

select avg(t.scores) result from t_examresults t where t.studentid = 6;          -- result:73.416666
select avg(distinct t.scores) result from t_examresults t where t.studentid = 6; -- result:74.300000

注意:avg 函数的返回值有可能会是 null 值,证明如下:

select avg(1) result;             -- result:1
select avg(1) result where 1 = 2; -- result:null

2.6、聚合函数小结

上文逐一讲述了常见五大聚合函数的基本语法和用途。其实这些聚合函数不仅可以单独使用,还可以结合起来使用。

示例一、查询第 1 次课程 2 考试成绩的统计结果:

select count(1) 参与人数,
    sum(t.scores) 总分,max(t.scores) 最高分,min(t.scores) 最低分,avg(t.scores) 平均分
from t_examresults t 
where t.counts = 1 and t.courseid = 2;

示例二、查询 1 班的学生总数及年龄统计结果:

with t as(
    select t.code,t.name,datediff(year,t.birthday,getdate()) age 
    from t_students t 
    where t.classid = 1
)
select count(1) 学生个数,max(t.age) 最大年龄,min(t.age) 最小年龄,avg(t.age) 平均年龄 
from t;

注意:本文所有关于聚合函数的示例,查询选择列表中包含的都是聚合函数表达式,没有一个字段,因为不允许,不过倒是可以包含与表字段无关的常量或变量。其实聚合函数通常与 group by 子句一起使用,而且也只有包含在 group by 子句中的字段才能出现在查询选择列表中,具体原因将在下一篇博文中具体讲述。

3、本文小结

本文主要讲述了 sql server 中的集合运算和聚合函数,以及它们的基本语法和用途。在集合运算中,union 和 union all 是比较常用的。而常见的 5 个聚合函数都比较常用。

本文参考链接:

本文链接
版权声明:本文为博客园博主 原创,作者保留署名权!欢迎通过转载、演绎或其它传播方式来使用本文,但必须在明显位置给出作者署名和本文链接!个人博客,能力有限,若有不当之处,敬请批评指正,谢谢!

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com