子查询与 CTE

-- 价格高于平均价的商品
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);
 
-- SELECT 列表里的标量子查询（每行执行一次，行多时要小心）
SELECT u.username,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- 买过「技术书」分类商品的订单
SELECT * FROM orders
WHERE id IN (
  SELECT oi.order_id
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  WHERE p.category_id = 5
);
 
-- 比「手机」分类所有商品都贵的商品
SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products WHERE category_id = 2);
 
-- 比「手机」分类里任意一个商品贵即可
SELECT name, price FROM products
WHERE price > ANY (SELECT price FROM products WHERE category_id = 2);

-- 买过「技术书」分类商品的订单
SELECT id, order_no, total_amount FROM orders
WHERE id IN (
  SELECT oi.order_id
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  WHERE p.category_id = 5
);
 
-- 每个用户的消费总额，再取平均：分两步的问题分两层写
SELECT AVG(total_spent) AS avg_user_spent
FROM (
  SELECT user_id, SUM(total_amount) AS total_spent
  FROM orders
  WHERE status IN (1,2,3)
  GROUP BY user_id
) AS t;   -- 派生表必须起别名

-- IN：先把子查询结果算出来，再逐行比对
SELECT * FROM users u
WHERE u.id IN (SELECT user_id FROM orders);
 
-- EXISTS：对外层每一行，探测子查询是否至少有一行（短路，找到即停）
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 正常：user_id 都非 NULL，能查出没下过单的用户
SELECT id, username FROM users
WHERE id NOT IN (SELECT user_id FROM orders);
 
-- 集合里混进一个 NULL，结果直接变空集
SELECT id, username FROM users
WHERE id NOT IN (1, 2, NULL);
 
-- 对 NULL 免疫的写法
SELECT id, username FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

WITH paid_orders AS (
  SELECT * FROM orders WHERE status IN (1,2,3)
),
user_spent AS (
  SELECT user_id, COUNT(*) AS cnt, SUM(total_amount) AS spent
  FROM paid_orders
  GROUP BY user_id
)
SELECT u.username, s.cnt, s.spent
FROM user_spent s
JOIN users u ON u.id = s.user_id
WHERE s.spent > 3000
ORDER BY s.spent DESC;

WITH RECURSIVE category_tree AS (
  -- 锚点：起始节点
  SELECT id, name, parent_id, 1 AS depth,
         CAST(name AS CHAR(200)) AS path
  FROM categories
  WHERE id = 1                        -- 从「电子产品」开始
 
  UNION ALL
 
  -- 递归部分：不断用上一轮结果去找子节点
  SELECT c.id, c.name, c.parent_id, t.depth + 1,
         CONCAT(t.path, ' > ', c.name)
  FROM categories c
  JOIN category_tree t ON c.parent_id = t.id
)
SELECT * FROM category_tree;

-- 生成 2026-07 整月的日期序列
WITH RECURSIVE dates AS (
  SELECT DATE('2026-07-01') AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2026-07-31'
)
SELECT d FROM dates;