LLM-test/test/sql/2-sql测试-1/deepseek-v3.2-web.md

我来设计一个具有复杂关联关系和迷惑项的电商数据库模型：

数据库：E-Commerce System

表设计（包含迷惑项）

1. 用户相关表

-- 用户主表（与客户表名称相似，容易混淆）
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(20),
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active'
);

-- 客户信息表（与用户表相似，但有不同用途）
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,  -- 外键，但名称与users表主键不完全匹配
    full_name VARCHAR(100),
    birth_date DATE,
    registration_date DATE,
    customer_type ENUM('individual', 'business', 'vip') DEFAULT 'individual',
    FOREIGN KEY (user_id) REFERENCES users(id)  -- 故意写错：应该是user_id REFERENCES users(user_id)
);

-- 用户地址表
CREATE TABLE user_addresses (
    address_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    customer_id INT,  -- 冗余字段，增加复杂度
    address_line1 VARCHAR(200),
    address_line2 VARCHAR(200),
    city_id INT,
    postal_code VARCHAR(20),
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

2. 商品相关表

-- 商品表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_code VARCHAR(50) UNIQUE NOT NULL,  -- 与sku相似
    product_name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2),  -- 与price相似
    category_id INT,
    supplier_id INT,
    stock_quantity INT DEFAULT 0,
    reorder_level INT DEFAULT 10,
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 产品表（名称与products相似，但内容不同）
CREATE TABLE items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    sku VARCHAR(50) UNIQUE,  -- 与product_code相似
    variant_name VARCHAR(100),
    additional_price DECIMAL(10,2) DEFAULT 0,
    inventory_count INT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 商品分类表
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) NOT NULL,
    parent_category_id INT,  -- 自引用，实现层级分类
    category_path VARCHAR(500),
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
);

3. 订单相关表

-- 订单主表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT NOT NULL,  -- 注意：这里引用customers表，不是users表
    user_id INT,  -- 冗余字段
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    total_amount DECIMAL(10,2),
    net_amount DECIMAL(10,2),  -- 与total_amount相似
    shipping_address_id INT,
    billing_address_id INT,
    payment_method VARCHAR(50)
);

-- 订单项表
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    item_id INT,  -- 引用items表
    product_id INT,  -- 也引用products表，增加复杂度
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    discount DECIMAL(10,2) DEFAULT 0,
    subtotal DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 发货表
CREATE TABLE shipments (
    shipment_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    shipment_date DATE,
    estimated_delivery DATE,  -- 与delivery_date相似
    actual_delivery DATE,  -- 与estimated_delivery相似
    carrier VARCHAR(100),
    tracking_number VARCHAR(100),
    status ENUM('preparing', 'shipped', 'in_transit', 'delivered') DEFAULT 'preparing'
);

4. 多对多关系表

-- 商品-供应商关系（多对多）
CREATE TABLE product_suppliers (
    product_id INT NOT NULL,
    supplier_id INT NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    purchase_price DECIMAL(10,2),
    PRIMARY KEY (product_id, supplier_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
    -- 故意不创建supplier_id的外键约束，增加测试难度
);

-- 用户-收藏夹关系（多对多）
CREATE TABLE user_wishlists (
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    added_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    priority INT DEFAULT 1,
    PRIMARY KEY (user_id, product_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

5. 其他相关表（增加迷惑性）

-- 支付表
CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    payment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    amount DECIMAL(10,2),
    payment_type VARCHAR(50),  -- 与payment_method相似
    transaction_id VARCHAR(100),
    payment_status ENUM('pending', 'completed', 'failed', 'refunded')
);

-- 评价表
CREATE TABLE reviews (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    review_date DATE,  -- 与created_at相似但不同
    helpful_count INT DEFAULT 0
);

-- 促销表
CREATE TABLE promotions (
    promo_id INT PRIMARY KEY AUTO_INCREMENT,
    promo_code VARCHAR(50) UNIQUE,
    promo_name VARCHAR(100),
    discount_type ENUM('percentage', 'fixed', 'buy_one_get_one'),
    discount_value DECIMAL(10,2),
    start_date DATE,
    end_date DATE,
    minimum_purchase DECIMAL(10,2)
);

迷惑项总结：

1. 相似表名：products/items, users/customers
2. 相似字段名：price/cost_price, total_amount/net_amount, estimated_delivery/actual_delivery
3. 含义相近字段：created_date/created_at/created_time, payment_type/payment_method
4. 冗余字段：order_items表中同时有item_id和product_id
5. 不一致的命名：user_id vs customer_id在不同表的引用
6. 缺失的外键：product_suppliers表中的supplier_id没有外键约束
7. 相似的用途不同名：product_code vs sku
8. 同义词字段：stock_quantity vs inventory_count

测试查询示例：

-- 复杂查询1：找出每个用户最常购买的商品类别
-- 需要连接：users → customers → orders → order_items → products → categories

-- 复杂查询2：计算每个供应商的商品在最近一个月的销售额
-- 需要连接：product_suppliers → products → order_items → orders

-- 复杂查询3：找出有相同商品的用户对（社交推荐）
-- 需要多对多关系分析：users → user_wishlists → products ← user_wishlists ← users

-- 复杂查询4：层级分类的商品统计（递归查询）
-- 需要使用WITH RECURSIVE处理categories表的层级关系

这个设计包含了：

• 一对多关系（用户-地址、订单-订单项）
• 多对多关系（用户-收藏夹、商品-供应商）
• 自引用关系（分类层级）
• 复杂的业务逻辑和数据冗余
• 各种迷惑项和陷阱

你可以用这个数据库模型来测试LLM对复杂SQL查询的理解和生成能力。