子查询与 CTE

SELECT name,
       (SELECT AVG(price) FROM core.products) AS 全局均价,
       price - (SELECT AVG(price) FROM core.products) AS 与均价差
FROM core.products;

-- IN：右子查询返回的值集合里是否存在
SELECT * FROM core.users
WHERE id IN (SELECT user_id FROM core.orders WHERE status='paid');
 
-- EXISTS：相关子查询，存在即真（常比 IN 更高效，尤其子结果大时）
SELECT u.* FROM core.users u
WHERE EXISTS (
  SELECT 1 FROM core.orders o WHERE o.user_id = u.id AND o.status='paid'
);

WITH paid_orders AS (
  SELECT user_id, SUM(total_amount) AS paid_sum
  FROM core.orders
  WHERE status = 'paid'
  GROUP BY user_id
)
SELECT u.username, p.paid_sum
FROM paid_orders p
JOIN core.users u ON u.id = p.user_id
ORDER BY p.paid_sum DESC;

WITH RECURSIVE tree AS (
  -- 锚点：从根节点开始
  SELECT id, name, parent_id, 1 AS depth
  FROM core.categories
  WHERE parent_id IS NULL
  UNION ALL
  -- 递归：把子节点接上来
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM core.categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT id, name, depth FROM tree ORDER BY depth, id;