mysql 假选择

MySQL假选择

sql
SELECT p.product_name, s.sales_quantity, r.comment
FROM products p
INNER JOIN (
    SELECT product_id, SUM(quantity) AS sales_quantity
    FROM sales
    GROUP BY product_id
) s ON p.product_id = s.product_id
LEFT JOIN (
    SELECT product_id, comment
    FROM reviews
    WHERE rating > 4
) r ON p.product_id = r.product_id
WHERE p.category = 'electronics';

sql
-- 创建商品表格
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50)
);
-- 创建销售表格
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- 创建用户评价表格
CREATE TABLE reviews (
    review_id INT PRIMARY KEY,
    product_id INT,
    comment TEXT,
    rating INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);