子查询

-- 外层查询
SELECT * FROM products
WHERE price > (
    -- 内层查询（子查询）
    SELECT AVG(price) FROM products
);

-- 查找价格高于平均价的商品
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 查找买过iPhone的用户
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders
    WHERE product_name LIKE '%iPhone%'
);

-- 先统计，再查询
SELECT category, avg_price
FROM (
    SELECT category, AVG(price) as avg_price
    FROM products
    GROUP BY category
) AS category_stats
WHERE avg_price > 100;

-- 查询用户及其文章数
SELECT 
    username,
    (SELECT COUNT(*) FROM posts WHERE user_id = users.id) as post_count
FROM users;

-- 查找销量最好的商品
SELECT name
FROM products
WHERE id IN (
    SELECT product_id
    FROM order_items
    GROUP BY product_id
    ORDER BY SUM(quantity) DESC
    LIMIT 5
);

-- 查找没有订单的用户
SELECT * FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

-- 等价于LEFT JOIN
SELECT u.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;