SQL多列相同的联接列

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

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

-- 插入示例数据
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 101, '2023-01-01', 150.00),
(2, 102, '2023-02-15', 200.00);

INSERT INTO customers (customer_id, customer_name, email) VALUES
(101, 'Alice Smith', 'alice@example.com'),
(102, 'Bob Johnson', 'bob@example.com');

-- 多列相同的联接查询
SELECT o.order_id, o.order_date, o.total_amount, c.customer_name, c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id AND o.order_id = c.customer_id;

-- 在 orders 表的 customer_id 和 order_id 列上创建索引
CREATE INDEX idx_orders_customer_order ON orders(customer_id, order_id);

-- 在 customers 表的 customer_id 列上创建索引
CREATE INDEX idx_customers_customer ON customers(customer_id);