如何在select子句中重用子查询？

SELECT 
    o.order_id,
    o.customer_id,
    (SELECT SUM(quantity * price) FROM order_details WHERE order_id = o.order_id) AS total_amount,
    (SELECT AVG(quantity * price) FROM order_details WHERE order_id = o.order_id) AS average_amount
FROM 
    orders o;

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT 
    cte_name.column1,
    cte_name.column2,
    ...
FROM 
    cte_name;

WITH order_amounts AS (
    SELECT 
        order_id,
        SUM(quantity * price) AS total_amount,
        AVG(quantity * price) AS average_amount
    FROM 
        order_details
    GROUP BY 
        order_id
)
SELECT 
    o.order_id,
    o.customer_id,
    oa.total_amount,
    oa.average_amount
FROM 
    orders o
JOIN 
    order_amounts oa ON o.order_id = oa.order_id;