SQL表视图连接返回最小值

-- 创建视图
CREATE VIEW customer_min_order AS
SELECT c.customer_id, MIN(o.amount) AS min_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

-- 创建示例表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO orders (order_id, customer_id, amount) VALUES
(101, 1, 100.00),
(102, 1, 50.00),
(103, 2, 75.00),
(104, 3, 200.00);

-- 创建视图
CREATE VIEW customer_min_order AS
SELECT c.customer_id, MIN(o.amount) AS min_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

-- 查询视图
SELECT * FROM customer_min_order;