当前位置：首页 > article >正文

Sql中WITH的作用

article 2025/1/8 2:27:19

1. `WITH` 子句：公共表表达式 (CTE)

WITH 子句是 SQL Server（以及许多现代数据库系统）中支持的功能，用于定义 公共表表达式（CTE）。CTE 是一个临时的结果集，查询在执行过程中可以像表一样引用它。

在你的例子中，WITH 子句定义了一个临时的查询结果，称为 RankedAttendance。这部分查询将用于后续的主查询。

RankedAttendance是你给公用表表达式（CTE）取的名字。
在 WITH 后面的大括号内，可以写一个普通的 SQL 查询，这个查询将返回一个结果集，作为 CTE 的内容。
在查询的后续部分，你可以像引用普通表一样引用这个 CTE。

WITH RankedAttendance AS ( 
-- CTE 查询部分 
SELECT PersonID, AttendanceDate, AttendanceStatus,
 ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY AttendanceDate DESC) AS RowNum 
FROM a )

2. CTE 的作用

CTE 中的查询为每个 PersonID（即每个人）根据 AttendanceDate（考勤日期）降序排序，使用了 ROW_NUMBER() 窗口函数来给每个考勤记录分配一个行号。ROW_NUMBER() 根据 PARTITION BY PersonID 和 ORDER BY AttendanceDate DESC 排序，使得每个人的最新记录会被分配 RowNum = 1。

ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY AttendanceDate DESC)：
这个窗口函数会按照 PersonID 分组，并对每组按 AttendanceDate 降序排列，为每个考勤记录分配一个唯一的行号（从 1 开始）。
- PARTITION BY PersonID：表示按 PersonID 对数据进行分组，确保每个人的考勤记录被单独排序。
- ORDER BY AttendanceDate DESC：对每个人的考勤记录按日期降序排列，确保最新的考勤记录排在最前面。

3. 主查询：从 CTE 中提取最新记录

SELECT PersonID, AttendanceDate, 
AttendanceStatus FROM RankedAttendance WHERE RowNum = 1;

在主查询中，我们从 CTE（RankedAttendance）中选择 PersonID、AttendanceDate 和 AttendanceStatus，并且使用 WHERE RowNum = 1 来过滤出每个人的最新一条考勤记录，因为每个人的最新记录的 RowNum 为 1。