mysql 中 case when 语法的使用

case when 是一种强大的流程控制语句，它允许在sql查询中实现条件逻辑判断，类似于编程语言中的if ... elif ... else 结构

主要有两种形式: 简单CASE表达式 和 搜索CASE表达式

简单CASE表达式

简单CASE表达式将字段或表达式的值与一系列预设的值进行比较

CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ELSE default_result] -- 可选
END

示例

SELECT
    employee_name,
    employee_level,
    CASE employee_level
        WHEN 'A' THEN '高级工程师'
        WHEN 'B' THEN '中级工程师'
        WHEN 'C' THEN '初级工程师'
        ELSE '实习生' -- 如果 employee_level 既不是 A, B, 也不是 C，则显示 '实习生'
    END AS level_description
FROM
    employees;

搜索CASE表达式

搜索CASE表达式允许您使用更复杂的条件（例如比较运算符> < = 或 逻辑运算符 AND OR）进行判断

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE default_result] -- 可选
END

示例

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary >= 80000 THEN '高薪酬'
        WHEN salary >= 50000 AND salary < 80000 THEN '中薪酬'
        -- 这里的 ELSE 会涵盖所有小于 50000 的情况
        ELSE '低薪酬'
    END AS pay_grade
FROM
    employees;

关键用法和注意事项

特性	描述
返回值	CASE 表达式的返回值可以是任何数据类型（字符串、数字、日期等），但所有 THEN 子句返回的类型应保持一致或可兼容。
ELSE 子句	ELSE 子句是可选的。如果省略 ELSE 子句，并且没有 WHEN 条件匹配，则 CASE 表达式会返回 NULL。建议总是包含 ELSE 子句以明确处理所有情况。
逻辑顺序	搜索 CASE 表达式是短路评估 (Short-circuit Evaluation)。它从上到下逐个检查 WHEN 条件，一旦条件为真，立即返回结果，后面的条件将不再执行。
可用于何处	CASE 表达式可以在 SQL 语句的几乎任何地方使用，包括 SELECT 列表、WHERE 子句、ORDER BY 子句和 GROUP BY 子句（但通常用于 SELECT）。

实用技巧

1. 在 ORDER BY 中实用

   -- 示例：将 'P0' 优先排在最前面，其他按默认顺序
   SELECT
       *
   FROM
       tasks
   ORDER BY
       CASE task_priority
           WHEN 'P0' THEN 1      -- P0 优先级最高
           WHEN 'P1' THEN 2
           WHEN 'P2' THEN 3
           ELSE 4                -- 其他优先级最低
       END,
       task_creation_date DESC;
   

sql 多表查询

多表查询是通过JOIN 操作符将两个或多个表中的行结合起来，基于它们之间的相关列，从而获取夸表的数据集合

1. JOIN类型

   JOIN 类型	关键字	描述	适用场景
   内连接	INNER JOIN	只返回两个表中相互匹配的行。如果某行在一个表中没有匹配项，则不返回。	仅需要匹配数据，排除不完整的数据。
   左连接	LEFT JOIN	返回左表（FROM 后面的表）中的所有行，以及右表中匹配的行。如果右表没有匹配，则右表列返回 NULL。	需要保留主表（左表）的所有记录，并查看其关联信息（无论是否存在）。
   右连接	RIGHT JOIN	返回右表中的所有行，以及左表中匹配的行。如果左表没有匹配，则左表列返回 NULL。	需要保留副表（右表）的所有记录，并查看其关联信息。
   全连接	FULL JOIN	返回左右两个表中的所有行。任一表中没有匹配的行，则另一表对应列返回 NULL。	需要查看两个表中所有的记录，无论它们是否匹配。（MySQL 不直接支持，通常用 LEFT JOIN UNION ALL RIGHT JOIN 模拟）
   交叉连接	CROSS JOIN	返回两个表的笛卡尔积，即左表的每一行与右表的每一行组合。	极少使用，除非需要生成所有可能的组合。

2. 语法结构

   SELECT
       A.column1,
       B.column2
   FROM
       TableA AS A -- 别名简化引用
   [INNER | LEFT | RIGHT] JOIN TableB AS B
   ON
       A.matching_column = B.matching_column -- 连接条件
   [WHERE
       A.column1 > value] -- 筛选条件
   

3. 示例：左连接

   SELECT
       d.department_name,
       COUNT(e.employee_id) AS employee_count
   FROM
       departments AS d
   LEFT JOIN
       employees AS e ON d.department_id = e.department_id
   GROUP BY
       d.department_name;
   

窗口函数

窗口函数是一种特殊的sql函数，它对与当前行相关的一组行（窗口）执行计算，但与聚合函数不同的是，它不会将结果集折叠成单个输出行

1. 语法

   $$FunctionName(...) \quad OVER \quad ([PARTITION \ BY \ expr] \ [ORDER \ BY \ expr] \ [frame]) $$

   关键词	作用	描述
   OVER()	必选	标识这是一个窗口函数，定义了窗口的范围和行为。
   PARTITION BY	可选	将行分成若干个独立的分区 (Partition)。窗口函数在每个分区内独立计算。
   ORDER BY	可选/必选	定义分区内行的计算顺序。对于排名和累计计算，这是必不可少的。
   frame	可选	精确定义当前行在窗口内的范围（例如：从第一行到当前行，或前后 $N$ 行）。

2. 常见的窗口函数类型

   类型	常用函数	描述	示例用法
   排名函数	ROW_NUMBER()	为分区内的每一行分配一个唯一的序号（不并列）。	ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC)
   	RANK()	为分区内的每一行分配一个等级（并列行等级相同，跳过下一个序号）。	RANK() OVER(PARTITION BY dept ORDER BY salary DESC)
   	DENSE_RANK()	为分区内的每一行分配一个等级（并列行等级相同，不跳过下一个序号）。	DENSE_RANK() OVER(ORDER BY sales DESC)
   聚合函数	SUM(), AVG(), COUNT()	在窗口或分区内进行累计或平均计算。	SUM(amount) OVER(PARTITION BY customer ORDER BY date) (计算累计消费)
   分析函数	LAG(col, n)	返回当前行前 $n$ 行的 col 值。	LAG(salary, 1) OVER(PARTITION BY dept ORDER BY hire_date) (比较前一天的销售额)
   	LEAD(col, n)	返回当前行后 $n$ 行的 col 值。	LEAD(date, 1) OVER(ORDER BY date) (计算下一个事件的日期差)
   	NTILE(n)	将分区内的行分成 $n$ 个等级（桶）。	NTILE(4) OVER(ORDER BY score DESC) (将学生分成四等份)

3. 示例应用：计算部门内排名

   # 计算每个部门员工的薪水排名（允许并列）
   SELECT
       employee_name,
       department_id,
       salary,
       RANK() OVER (
           PARTITION BY department_id -- 按部门分组
           ORDER BY salary DESC       -- 薪水高的排前面
       ) AS rank_in_dept
   FROM
       employees;
   

4. 窗口帧

   窗口帧是 OVER() 子句中的可选部分，用于进一步限制分区内计算的行范围，常见格式为：

   ROWS BETWEEN [start_bound] AND [end_bound]
   

   常用边界：

   • UNBOUNDED PRECEDING：分区的第一行。
   • CURRENT ROW：当前行。
   • UNBOUNDED FOLLOWING：分区的最后一行。

   示例：计算移动平均 (Moving Average)

   计算当前行和前两行的销售额平均值：

   SELECT
       sale_date,
       sale_amount,
       AVG(sale_amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 范围：前两行 + 当前行 (共三行)
       ) AS three_day_avg
   FROM
       sales;