sqlserver 联表查询、子查询、窗口函数、聚合函数等概念与例子

with cte as的用法

查询的一个有用工具，允许创建临时命名结果集，可在查询中多次引用相同的子查询结果，可以提高查询的可读性和维护性

WITH cte_name (column1, column2, ...) AS (
    -- 这里是子查询
    SELECT column1, column2, ...
    FROM your_table
    WHERE some_condition
)
-- 主查询
SELECT *
FROM cte_name;

1
2
3
4
5
6
7
8
9
10

子查询

• 标量子查询：返回单个值，通常用于比较运算符中，他们嵌套在主查询的条件中

-- 查找所有工资高于公司平均工资的员工
SELECT employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

1
2
3
4
5

• 行子查询： 返回单行多列，通常将子查询的结果集整行引入主查询中
• 列子查询：返回单列多行，通常将子查询结果集作为一个值列表传递给主查询。
• 表子查询：多表联查

窗口函数

rank over (partition by order_code order by dd desc)
partition by 指定分区的表达式，如果不指定，整个结果集将作为一个分区，指定分区将使排名在每个分区内单独计算
注意：rank()函数可能导致排名值不连续，多个行具有相同的值，将共享相同的排名，使用dense_rank()可去重

窗口函数原理

在不破坏原始数据行的情况下对每一行进行聚合或分析
排序（order by）、分组(partition by)、计算（针对分组内数据行，而不是整个结果集）、结果（窗口函数的结果值附加到每一行，意味着每个数据行都有一个关联的窗口函数结果）

常用聚合函数

sum、arg等

窗口函数分类

排名函数
SELECT employee_name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS ranking
FROM employees;

累积函数
SELECT order_date, order_amount, SUM(order_amount) OVER (ORDER BY order_date) AS cumulative_total
FROM orders;

分组聚合函数
SELECT employee_name, department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

行号函数（通常用于合并字段值相同的行）
SELECT product_name, ROW_NUMBER() OVER (ORDER BY sales_volume DESC) AS row_num
FROM products;