SQL语法 ROW_NUMBER, WITH ... AS

 <select id="selectValuationTrend" resultType="DataVO">
        WITH ranked_data AS (
            SELECT
                CASE
                    WHEN #{dateType} = 1 THEN TO_CHAR(fes.VALUATION_DATE, 'YYYY-MM')
                    WHEN #{dateType} = 2 THEN TO_CHAR(fes.VALUATION_DATE, 'YYYY') || '-Q' || TO_CHAR(fes.VALUATION_DATE, 'Q')
                    WHEN #{dateType} = 3 THEN TO_CHAR(fes.VALUATION_DATE, 'YYYY')
                END AS timeDimension,
                nvl(fes.COMPANY_VALUATION, 0) AS companyValuation,
                nvl(fes.UNIT_PRICE, 0) AS unitPrice,
                ROW_NUMBER() OVER (
                    PARTITION BY
                        CASE
                            WHEN #{dateType} = 1 THEN TO_CHAR(fes.VALUATION_DATE, 'YYYY-MM')
                            WHEN #{dateType} = 2 THEN TO_CHAR(fes.VALUATION_DATE, 'YYYY') || '-Q' || TO_CHAR(fes.VALUATION_DATE, 'Q')
                            WHEN #{dateType} = 3 THEN TO_CHAR(fes.VALUATION_DATE, 'YYYY')
                        END
                    ORDER BY fes.VALUATION_DATE DESC
                ) AS rn
            FROM EI.ENTERPRISE_VALUATION_STATICS fes
            WHERE fes.ENTERPRISE_ID = #{enterpriseId}
              AND TO_CHAR(fes.VALUATION_DATE, 'YYYY') BETWEEN #{startDate} AND #{endDate}
        )
        SELECT timeDimension, companyValuation, unitPrice
        FROM ranked_data
        WHERE rn = 1
        ORDER BY timeDimension ASC
    </select>

上面有个完整的语法, 我们学习一下

WITH…AS

简单来说，它的作用是在执行主查询之前，先定义一个“临时结果集”。你可以把它理解为一个只在当前这条 SQL 语句中生效的临时表或视图.

提升可读性：把复杂的逻辑（如多层嵌套的子查询）拆分成一个个有名字的步骤，像写代码一样清晰

在这个例子里ranked_data 就相当于一个临时表或视图, 在其他语句里可以当子语句
oracle 12c 开始支持, mysql 8.0 支持

WITH 
    cte_name1 AS (
        -- 第一个临时结果集
        SELECT col1, col2 FROM table1 WHERE ...
    ),
    cte_name2 AS (
        -- 第二个临时结果集（可以引用 cte_name1）
        SELECT col3, col4 FROM cte_name1 WHERE ...
    )
-- 主查询（像使用普通表一样使用 CTE）
SELECT * 
FROM cte_name2;

CASE WHEN THEN ELSE END

SELECT ProductNumber, Name,
    CASE
        WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
        WHEN ListPrice < 50 THEN 'Under $50'
        WHEN ListPrice >= 50 AND ListPrice < 250 THEN 'Under $250'
        WHEN ListPrice >= 250 AND ListPrice < 1000 THEN 'Under $1000'
        ELSE 'Over $1000'
    END AS Price_Range
FROM Production.Product;

这是一个字段的判断显示, 之前介绍过, 我们这略过

ROW_NUMBER() OVER (PARTITION BY)

ROW_NUMBER() OVER (
    [PARTITION BY 分组字段] 
    ORDER BY 排序字段 [ASC/DESC]
) AS 别名

和group by 一个明细的区别是, group by 的结果会减少行数,而 这个row_number() 不会, 他的主要目的就是为每一行添加一个序号, 在加需要的基础上还可以根据partition by分组

例子

department	name	salary
IT	Alice	15000
IT	Bob	12000
IT	Charlie	15000
HR	David	8000
HR	Eve	9000

示例 1：全局排序（不带 PARTITION BY）

SELECT 
    department, 
    name, 
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS global_rank
FROM employees;

department	name	salary	global_rank
IT	Alice	15000	1
IT	Charlie	15000	2
IT	Bob	12000	3
HR	Eve	9000	4
HR	David	8000	5
(注：Alice 和 Charlie 薪水一样，但 ROW_NUMBER() 依然会强制给出 1 和 2)

示例 2：分组内排名（带 PARTITION BY）最常用

SELECT 
    department, 
    name, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

department	name	salary	dept_rank
HR	Eve	9000	1
HR	David	8000	2
IT	Alice	15000	1
IT	Charlie	15000	2
IT	Bob	12000	3
(注：注意看 IT 和 HR 部门各自都从 1 开始重新编号了！)

示例 3：实战场景 —— 找出每个部门薪水最高的员工（Group Top-N）

需求：只保留每个部门里薪水最高的那 1 个人。

SELECT * FROM (
    SELECT 
        department, 
        name, 
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn = 1;  -- 直接在外层过滤，只要排名第 1 的

department	name	salary	rn
HR	Eve	9000	1
IT	Alice	15000	1

在最上面的例子里, 我们不是用具体的一个字段来分组的, 而是
这段代码的核心作用是：根据不同的时间粒度（月、季、年），对估值数据进行分组，并在每个分组内按时间倒序生成行号

• 当 #{dateType} = 1（按月分组）
  CASE WHEN 返回 TO_CHAR(…, ‘YYYY-MM’)
  分组键：2023-11
  效果：所有 2023 年 11 月的数据会被分在同一组，组内按具体日期倒序排列。
• 当 #{dateType} = 2（按季度分组）
  CASE WHEN 返回 TO_CHAR(…, ‘YYYY’) || ‘-Q’ || TO_CHAR(…, ‘Q’)
  分组键：2023-Q4
  效果：所有 2023 年第四季度的数据会被分在同一组，组内按具体日期倒序排列。
• 当 #{dateType} = 3（按年分组）
  CASE WHEN 返回 TO_CHAR(…, ‘YYYY’)
  分组键：2023
  效果：所有 2023 年的数据会被分在同一组，组内按具体日期倒序排列。

注意 || 不是或 而是sql里的拼接符

case when 写了2次 一次是作为字符输出, 一次作废 标序且分组的

结合了 CTE (WITH)、ROW_NUMBER() 窗口函数以及 MyBatis 的动态参数，实现了一个高度动态的“按时间粒度取最新记录”的业务逻辑。