MySQL:来自子查询的总和值

本文介绍了MySQL:来自子查询的总和值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

是否可以将子查询中的两个值相加?

Is it possible to sum two values from subqueries?

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

I need select three values: total_view, total_comments and rating.

两个子查询都非常复杂，所以我不想重复.

Both subqueries is very complicated, so i don't wish duplicate it.

我的查询示例:

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

推荐答案

我建议使用子查询:

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是在一个计算列上，它无论如何都需要对数据进行排序，所以物化不是额外的开销.

MySQL materializes the subquery. But because the ORDER BY is on a computed column, it needs to sort the data anyway, so the materialization is not extra overhead.

这篇关于MySQL:来自子查询的总和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Subqueries