php - 使用sql从数据库中选择数据

Question

我有一个 sql 子选择来列出有多少订单及其总值(value):

SELECT u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id=u.id
  ) AS order_count,
  (SELECT SUM(oi.quantity * p.price)
  FROM orders            AS o
  INNER JOIN order_items AS oi
  ON oi.order_id = o.id
  INNER JOIN products AS p
  ON p.id        = oi.product_id
  WHERE o.user_id=u.id
  ) AS total_price
FROM users u
ORDER BY total_price DESC

有什么想法如何在不进行子选择的情况下做到这一点吗？

Answer 1

您可以将相关子查询(O(N^2) 性能)转换为 LEFT JOIN

SELECT u.name,
  o.order_count,
  o2.total_price
FROM users u left join (
  SELECT user_id, COUNT(*) order_count
  FROM orders
  GROUP BY user_id
) o
on u.id = o.user_id
left join (
  SELECT o.user_id, SUM(oi.quantity * p.price) total_price
  FROM orders o
  INNER JOIN order_items oi
  ON oi.order_id = o.id
  INNER JOIN products p
  ON p.id        = oi.product_id
  GROUP BY o.user_id
) o2 on u.id = o2.user_id
ORDER BY total_price DESC;