mysql 循环查询

-- 使用子查询
SELECT 
    o.order_id, 
    (SELECT SUM(oi.quantity * oi.price) FROM order_items oi WHERE oi.order_id = o.order_id) AS total_amount
FROM 
    orders o;

-- 使用连接（JOIN）
SELECT 
    o.order_id, 
    SUM(oi.quantity * oi.price) AS total_amount
FROM 
    orders o
JOIN 
    order_items oi ON o.order_id = oi.order_id
GROUP BY 
    o.order_id;

-- 使用存储过程
DELIMITER //

CREATE PROCEDURE CalculateTotalAmount(IN order_id INT, OUT total_amount DECIMAL(10, 2))
BEGIN
    SELECT SUM(oi.quantity * oi.price) INTO total_amount
    FROM order_items oi
    WHERE oi.order_id = order_id;
END //

DELIMITER ;

CALL CalculateTotalAmount(1, @total_amount);
SELECT @total_amount;