SQL总复习二：窗口函数、集合运算、CASE表达式的妙用

窗口函数

窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作，即对数据库数据进行实时分析处理。

窗口函数语法：

<窗口函数> OVER ([PARTITION BY <列清单>]
                         ORDER BY <排序用列清单>)

我们先来列举一下能够作为窗口函数使用的函数。窗口函数大体可以分为以下两种：

① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）

② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

聚合函数作为窗口函数使用

他这个特点，就是排序后，以自身和之前的行，进行指定聚合函数的累计计算。

SELECT product_id, product_name, sale_price,
 SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
 FROM Product;

image-20210822165932648

专用窗口函数：

注意，这些函数是无参的，如rank（），括号里空着就行。

接下来看一个窗口函数的示例，rank() 函数：

image

这里使用rank（）函数生成了一个新的列用于表示相应记录的序号。整体来看，查询结果中先按照省份进行划定范围（就像分组一样），然后再对各个范围里的数据依据身高降序进行排列，而Ranking这个列则是连续的序号而已。so，从差查询出的结果来看，rank（）函数就是用来在不同的范围（分组）内，对里面的记录标记序号的。另外，可以看出 PARTITION BY 和 ORDER BY 的作用了，如上图所示，PARTITION BY 在横向上对表进行分组，而 ORDER BY 决定了纵向排序的规则。还有，PARTITION BY 那句是可以省略掉的，即对全部数据生成相应的“行号”。无论使不使用窗口函数，查询出的结果的记录数总是和不使用时一样。通过PARTITION BY分组后的记录集合称为“窗口”。

不同窗口函数在生成的序号方面的区别：

  • RANK函数

    计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

    例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……

  • DENSE_RANK函数

    同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

    例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……

  • ROW_NUMBER函数

    赋予唯一的连续位次。

    例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

具体来说就是这个样子，注意身高和序号：

image

窗口函数在SQL语句中的位置：

窗口函数只能使用在SELECT 子句之中。反过来说，就是这类函数不能在 WHERE 子句或者 GROUP BY 子句中使用。

如下面的使用方式是错误的：

select Salary
from Employee 
where (row_number() over (order by Salary desc))<10  --错误写法

集合运算

UNION（并集）、INTERSECT（交集）、EXCEPT（差集）

使用UNION（并集）、INTERSECT（交集）、EXCEPT（差集）等集合运算符来进行集合运算，表、视图和查询的执行结果都是记录的集合。

这些集合运算的特征就是以行方向为单位进行操作。

注意事项 :

作为运算对象的记录的列数必须相同，且各列的顺序类型上下一致

可以使用任何 SELECT 语句，但 ORDER BY 子句只能在最后使用一次

另外，集合运算符会除去重复的记录，即重复的只取一条。

先来看看示例中学生表和老师表的表字段和数据：

表中涉及到的类型有：int/date/narchar/decimal 这几个

image

union的使用：

select [Name] as PersonName,Birhtday as PersonBirthday from dbo.T_Student
union
select TeacherName,TeacherBirthday from dbo.T_Teacher

执行结果：

image

看吧，很明显，union就是把在两个或多个表的查询结果结合成了一个查询结果即多合一，类似于数学上的并集的这个东西。还有一个细节要注意，本来是学生和老师中各有四条记录，张靓颖这家伙既是学生也是老师，union就只取了一次，so，是不是和数学上的并集贼一样啊。还有union前后的select语句，其中的列的个数、列的类型和顺序都是要上下对应的，很符合实际。

另外你要是不想把重复数据去掉，即你就想得到两次张靓颖怎么办嘞？用union all就成了：

select [Name] as PersonName,Birhtday as PersonBirthday from dbo.T_Student
union all
select TeacherName,TeacherBirthday from dbo.T_Teacher

image

看，现在张靓颖出现了两次。另外，UNION（并集）、INTERSECT（交集）、EXCEPT（差集）等集合运算符中，后面跟个all都可以实现保留重复数据的效果，有几个就查询出几个。

注意：标准 SQL 规定，INTERSECT 比 UNION 和 EXCEPT 优先级更高。因此，当同时使用 UNION 和 INTERSECT，又想让 UNION 优先执行时，必须用括号明确地指定运算顺序

INTERSECT 应用于两张表，选取出它们当中的公共记录。

image

注意：这个公共记录是要根据你select的结果来看的，即先出来intersect前后select的结果，再从两个结果集合中挑选出相同的部分作为最终查询结果。

except 应用于两张表，类似于减法，即选取出前表中除了两表交集之外的部分。

image

注意：except前后select语句的位置互换，结果会不同，这就好比4-2的结果和2-4的结果并不相等一样。

JOIN以列为单位对表进行联结

联结（JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。该操作通常用于无法从一张表中获取期望数据（列）的情况。实际上，期望得到的数据往往会分散在不同的表之中。使用联结就可以从多张表（3 张以上的表也没关系）中选取数据了。

比如有以下两张表：

image

inner join（交集运算）

由于内联结只能选取出同时存在于两张表中的数据（根据On后面的条件），即相当于取两个集合的交集。

比如选取出姓张的学生信息，要求包含家长联系方式和高中毕业学校信息，那么你此时只能从学生表里取部分数据，再从T_StuExtend表里取另一部分数据，具体怎么办呢？此时就用这个inner join试试吧：

image

这里有个点需要注意：就是如果inner后面必须有on的条件，on后面跟的是连接两个表的点，比如上面示例中就是通过StudentID来联系的，另外on后面还可以跟更多的条件，如：on dbo.T_StuExtend.StudentID=dbo.T_Student.StudentID and dbo.T_Student.Gender=0。这样会取出某性别的记录。

inner join会取出所有的符合on后面的条件的数据，并根据该条件将on两边的行记录进行整合，如果没有匹配到数据，则不会记录到结果中去。

left outer join、right outer join

以上outer关键字可以省略，左联结的时候，结果行数 ≥ 左表行数（如果两表通过联结条件是严格1对1的话就是等于，如果右表有多个满足条件的，则记录也会多出几个），右联结同理。

student表里有4条数据，StuExtend表里有5条数据， select * from T_Student left outer join T_StuExtend ，结果将会是这样的，一看就明白了，每行记录就是根据on后面的连接条件，将join后面的表中的该条记录加到join前面的表的右边共同形成一条新的记录：

image

再看看right outer join，与之前相反，如下：

image

注意点：不管left还是right，都少不了on后面的“连接纽带”，即联结条件，这个纽带是进行join的基础。另外如果某条记录在另一个表中没有这个纽带，比如T_StuExtend表中有个学生ID是5，但是T_StuExtend表中没有studentID列的值为5的记录。

那么，如果是 T_Student left outer join T_StuExtend ，那么studentID为5的这条记录中不会出现在结果中。

如果是 T_Student right outer join T_StuExtend ，那么studentID为5的这条记录中不会出现在结果中，且该条记录中T_Student表的所有列为null。

总结：

left/right outer join，前者主表在左，后者主表在右。主表中的行记录会在结果中全部体现，不管通过on条件在另一个表中有没有关联到，没关联到时另一表的相关数据列在结果中为null。

如果主表某个记录，通过on联结的条件在另一表匹配的多条（比如说N条）记录，则结果中也会出现N条相关记录（左边是主表中该行的列数据，右边是另一表中匹配到的不同行的相关列数据）

内连接和外连接的区别在于，on的条件如果在两个表无法建立，则内连接不会出现该记录，而外连接会出现该记录，只是另一个表的相关列全为null而已。

COALESCE(value1,value2……) ，这是SQL 的标准函数，可以接受多个参数，功能是返回第一个非 NULL 的参数。

自联结

针对相同的表进行的连接被称为“自连接”,自连接得到的结果行数为单表行数的平方。他的执行结果跟 cross join 两个相同的表的结果一样。

写法：

SELECT P1.name AS name_1, P2.name AS name_2
 FROM Products P1, Products P2;
 --等同于
 SELECT P1.name AS name_1, P2.name AS name_2
 FROM Products P1 cross join Products P2;

image-20210916150740140

image-20210916152041265

all、any、in在列方面的量化

EXISTS 谓词主要用于进行“行方向”的量化，列方向上的量化就要用到比如 all 了。

比如有张表T_Log记录每月迟到次数，有14个列，UserID，RealName，LateCount1，LateCount2，……，LateCount12

如果要查询出每月都迟到的人，可以：

select RealName from T_Log where 1<= all ( LateCount1,LateCount2,……，LateCount12 )

如果要查询出是否有迟到5次的人，可以：

select RealName from T_Log where 5 = any ( LateCount1,LateCount2,……，LateCount12 )

或者：select RealName from T_Log where 5 in ( LateCount1,LateCount2,……，LateCount12 )

EXISTS 是为了实现谓词逻辑中“量化”（quantification）这一强大功能而被引入 SQL 的。

SQL中的case表达式的作用是用来对“某个变量”进行某种转化，通常在select字句中使用

case表达式基本用法

举个例子：

image

不难看出，case表达式很像我们的 if else 的作用，在发现为真的 WHEN 子句时，CASE 表达式的真假值判断就会中止，而剩余的 WHEN 子句会被忽略。case表达式有两种写法：

CASE sex
  WHEN '1' THEN '男'
  WHEN '2' THEN '女'
ELSE '其他' END
CASE WHEN sex = '1' THEN '男'
   WHEN sex = '2' THEN '女'
ELSE '其他' END
  • else这句非必须但最好带上，如果when中没有符合条件的，且有没有else控制，那么，这样的case会返回null，null不是个好东西，所以你一定要尽量控制减少返回null的情况。
  • 简单点说，在能写列名和常量的地方，通常都可以写 CASE 表达式，因为它返回的是一个标量值。

使用case实现不同条件的统计

有如下一个表：

image

上图中，下半部分查询结果如何得到呢？即如果让你查询出各个省份的男生总数和女生总数你会怎么搞呢？

看看如何用case实现的：

--查看所有学生信息
select StudentID,[Name],Province,Gender,(case Gender
                                    when 0 then N'女'
                                    when 1 then N'男'
                                    else '' end) StuGender
from dbo.T_Student
 
--查询出各省的男女生总数：
select Province,
sum(case when Gender=1 then 1 else 0 end ) as MaleCount,
sum(case when Gender=0 then 1 else 0 end ) as FeMaleCount
from dbo.T_Student
group by Province

在update操作中使用case条件分支，对不同范围的数据执行更新

比如有个员工薪资表Salaries，工资字段为salary ，

假设现在需要根据以下条件对该表的数据进行更新。

  1. 对当前工资为 30 万日元以上的员工，降薪 10%。
  2. 对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪 20%。

那么更新的SQL语句可以这样写：

-- 用CASE 表达式写分段的更新操作
UPDATE Salaries
   SET salary = CASE WHEN salary >= 300000
                     THEN salary * 0.9
                     WHEN salary >= 250000 AND salary < 280000
                     THEN salary * 1.2
                     ELSE salay END;

生成每月数据统计报表

有以下两张表：

image-20210828165157670

那么SQL语句可以这样写：

-- 表的匹配：使用IN 谓词
SELECT course_name,
       CASE WHEN course_id IN
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 200706) THEN '○'
            ELSE '×' END AS "6 月",
       CASE WHEN course_id IN
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 200707) THEN '○'
            ELSE '×' END AS "7 月",
       CASE WHEN course_id IN
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 200708) THEN '○'
            ELSE '×' END AS "8 月"
  FROM CourseMaster;
 
-- 表的匹配：使用EXISTS 谓词
SELECT CM.course_name,
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200706
                        AND OC.course_id = CM.course_id) THEN '○'
            ELSE '×' END AS "6 月",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200707
                        AND OC.course_id = CM.course_id) THEN '○'
            ELSE '×' END AS "7 月",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200708
                        AND OC.course_id = CM.course_id) THEN '○'
            ELSE '×' END  AS "8 月"
  FROM CourseMaster CM;

case与行转换成列

image-20210914231956941

SELECT name,
 CASE WHEN SUM(CASE WHEN course = 'SQL 入门' THEN 1 ELSE NULL END) = 1
 THEN '○' ELSE NULL END AS "SQL 入门",
 CASE WHEN SUM(CASE WHEN course = 'UNIX 基础' THEN 1 ELSE NULL END) = 1
 THEN '○' ELSE NULL END AS "UNIX 基础",
 CASE WHEN SUM(CASE WHEN course = 'Java 中级' THEN 1 ELSE NULL END) = 1
 THEN '○' ELSE NULL END AS "Java 中级 "
 FROM Courses
 GROUP BY name;

case与null

当case使用的变量或列的值可能为null时，唯一正确的使用方式如下：

CASE 
	WHEN col_1 = 1 THEN '○'
 	WHEN col_1 IS NULL THEN '×'
END
 --而不是：
CASE col_1
 WHEN 1 THEN '○'
 WHEN NULL THEN '×'
END

推荐SQL教程书：图灵社区的《SQL基础教程》、《SQL进价教程》

更新于：2023-5-17

posted @   AI大胜  阅读(332)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛