MySQL SUM() 带有 MIN() 的子查询

Question

首先，我想要完成的任务是：

获取 etp_product.price 和 option_price 的总和。

SELECT 
  etp_product.product_id,
  etp_product.price,
  (SELECT MIN(price) AS FIELD_2 FROM etp_product_option_value pov WHERE pov.product_id = etp_product.product_id) AS option_price
FROM
  etp_product
GROUP BY
  etp_product.product_id,
  etp_product.price

我已经尝试过：

SELECT 
  etp_product.product_id,
  etp_product.price,
  (SELECT MIN(price) AS FIELD_2 FROM etp_product_option_value pov WHERE pov.product_id = etp_product.product_id) AS option_price,
SUM(etp_product.price + option_price) AS Total
FROM
  etp_product
GROUP BY
  etp_product.product_id,
  etp_product.price

但出现此错误：

“字段列表”中存在未知列“option_price”

知道如何完成吗？

Answer 1

SELECT 
  p.product_id,
  p.price,
  IFNULL(MIN(pv.price), 0) as option_price,
  (p.price + IFNULL(MIN(pv.price), 0)) as total
FROM etp_product p
JOIN etp_product_option_value pv ON WHERE pv.product_id = p.product_id
GROUP BY
  p.product_id,
  p.price

Answer 2

SELECT 
etp_product.product_id,
  etp_product.price,
  (SELECT MIN(price) AS FIELD_2 FROM etp_product_option_value pov WHERE pov.product_id = etp_product.product_id) AS option_price,
 etp_product.price + (SELECT MIN(price) AS FIELD_3 FROM etp_product_option_value pov WHERE pov.product_id = etp_product.product_id) AS Total
FROM
  etp_product
GROUP BY
  etp_product.product_id,
  etp_product.price