MySQL:从子查询求和值

Question

是否可以对子查询中的两个值求和？

我需要选择三个值:total_view、total_comments 和 rating。

两个子查询都非常复杂，所以我不希望重复它。

我的查询示例:

SELECT p.id,
(
    FIRST subquery  
) AS total_view,
(
    SECOND subquery 
) AS total_comments,
(
    total_view * total_comments
) AS rating
FROM products p
WHERE p.status = "1"
ORDER BY rating DESC

Answer 1

我建议使用子查询:

SELECT p.*, (total_view * total_comments) as rating
FROM (SELECT p.id,
             (FIRST subquery) AS total_view,
             (SECOND subquery) AS total_comments,
      FROM products p
      WHERE p.status = '1'  -- if status is a number, then remove quotes
     ) p
ORDER BY rating DESC;

MySQL 实现子查询。但是因为 ORDER BY 在计算列上，它无论如何都需要对数据进行排序，因此物化不是额外的开销。