编辑代码

CREATE DATABASE my_database;
use my_database;
CREATE TABLE orders (
    order_id INT PRIMARY KEY,         
    customer_id INT NOT NULL,         
    order_date DATE NOT NULL,         
    total_amount DECIMAL(10, 2) NOT NULL  
);

INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES
    (1, 101, '2023-03-15', 500.00),
    (2, 102, '2023-05-20', 1200.00),
    (3, 101, '2023-07-10', 800.00),
    (4, 103, '2023-09-05', 300.00),
    (5, 102, '2023-11-12', 1500.00);


SELECT 
    customer_id, 
    SUM(total_amount) AS total_order_amount  -- 计算每个客户的订单总金额
FROM 
    orders
WHERE 
    order_date >= '2023-01-01' AND order_date <= '2023-12-31'  -- 筛选2023年的订单
GROUP BY 
    customer_id  -- 按客户ID分组
HAVING 
    SUM(total_amount) > 1000  -- 筛选订单总金额大于1000的客户
ORDER BY 
    total_order_amount DESC;  -- 按订单总金额降序排序