mysql 展平各列

-- 创建 users 表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_name VARCHAR(100),
    order_date DATE
);

-- 插入示例数据
INSERT INTO users (user_id, username, email) VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob', 'bob@example.com');

INSERT INTO orders (order_id, user_id, product_name, order_date) VALUES
(101, 1, 'Product A', '2023-01-01'),
(102, 1, 'Product B', '2023-01-05'),
(103, 2, 'Product C', '2023-01-10');

-- 展平 users 和 orders 表
SELECT 
    u.user_id,
    u.username,
    u.email,
    o.order_id,
    o.product_name,
    o.order_date
FROM 
    users u
JOIN 
    orders o ON u.user_id = o.user_id;

SELECT DISTINCT 
    u.user_id,
    u.username,
    u.email,
    o.order_id,
    o.product_name,
    o.order_date
FROM 
    users u
JOIN 
    orders o ON u.user_id = o.user_id;

-- 使用索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 分页查询
SELECT 
    u.user_id,
    u.username,
    u.email,
    o.order_id,
    o.product_name,
    o.order_date
FROM 
    users u
JOIN 
    orders o ON u.user_id = o.user_id
LIMIT 10 OFFSET 0;