1.WITH

在 MySQL 中，WITH 子句通常与递归查询或公共表达式（Common Table Expressions，简称 CTEs）一起使用，它允许你定义一个临时的结果集，这个结果集可以在查询的其余部分中被引用。这有助于将复杂的查询分解为更小、更易于管理的部分。
WITH 子句在 MySQL 8.0 及更高版本中可用。
举例：

WITH cte_name AS (
    SELECT column1, column2
    FROM some_table
    WHERE some_condition
)
SELECT * FROM cte_name;

2.WHEN CASE

SELECT
    product_id,
    product_name,
    status,
    CASE 
        WHEN status = 'active' THEN '产品正在销售'
        WHEN status = 'inactive' THEN '产品已下架'
        WHEN status = 'out_of_stock' THEN '产品缺货'
        ELSE '未知状态'
    END AS status_description
FROM products;

WHEN 也可以与聚合函数（如 SUM 或 AVG）一起使用在 CASE 语句中，以对满足特定条件的行进行条件聚合。

WITH UserConsumption AS (  
    SELECT  
        ue.user_id,  
        SUM(ue.expense_amount) AS total_annual_consumption,  
        SUM(ue.expense_amount) / 12 AS average_monthly_consumption,  
        SUM(CASE WHEN ue.expense_category = '交通' THEN ue.expense_amount ELSE 0 END) AS transportation_expense,  
        SUM(CASE WHEN ue.expense_category = '购物' THEN ue.expense_amount ELSE 0 END) AS shopping_expense,  
        SUM(CASE WHEN ue.expense_category = '住宿' THEN ue.expense_amount ELSE 0 END) AS accommodation_expense,  
        SUM(CASE WHEN ue.expense_category = '吃饭' THEN ue.expense_amount ELSE 0 END) AS dining_expense  
    FROM  
        user_expenses ue  
    WHERE  
        ue.expense_date BETWEEN '2023-01-01' AND '2023-12-31'  
    GROUP BY
        ue.user_id 
),
ConsumptionPercentages AS (
    SELECT  
        user_id,  
        total_annual_consumption,  
        average_monthly_consumption,  
        (transportation_expense / total_annual_consumption) * 100 AS transportation_percentage,  
        (shopping_expense / total_annual_consumption) * 100 AS shopping_percentage,  
        (accommodation_expense / total_annual_consumption) * 100 AS accommodation_percentage,  
        (dining_expense / total_annual_consumption) * 100 AS dining_percentage
    FROM
        UserConsumption
)
SELECT
    ui.user_id,
    ui.name,  
    cp.total_annual_consumption,  
    cp.average_monthly_consumption,  
    cp.transportation_percentage,  
    cp.shopping_percentage,  
    cp.accommodation_percentage,  
    cp.dining_percentage  
FROM  
    users_info ui  
JOIN  
    ConsumptionPercentages cp ON ui.user_id = cp.user_id  
ORDER BY  
    cp.total_annual_consumption DESC, ui.user_id ASC  
LIMIT 3;

3. ROUND

在 SQL 中，ROUND 函数用于将数值字段四舍五入到指定的小数位数或整数。它可以用于处理浮点数或小数，并返回四舍五入后的结果。

ROUND(number, decimal_places)

number 是要四舍五入的数值。
decimal_places 是可选参数，表示要四舍五入到的小数位数。如果省略，则number将被四舍五入到最接近的整数。