如何将多个子查询的结果相加？

Question

我正在运行一个MySQL查询，根据用户贡献的书评和食谱评论数量对站点的用户进行排名。在最初遇到多连接查询的问题之后，我切换到了一系列的子查询，这要快得多。然而，虽然我可以提取每个成员的评论数量，但我不知道如何将它们相加，以便按总数排序。

下面是当前的查询：

SELECT users.*,
   (SELECT count(*) FROM bookshelf WHERE bookshelf.user_id = users.ID) as titles,
   (SELECT count(*) FROM book_reviews WHERE book_reviews.user_id = users.ID) as bookreviews,
   (SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID) as recipereviews
FROM users   

我需要把书评和评论加在一起才能得到‘reviewtotal’。MySQL不允许使用简单的语法对别名进行计算，但我认为还有另一种方法可以做到这一点？？

Answer 1

将其包装到子查询中：

SELECT  *,
        bookreviews + recipereviews AS totalreviews
FROM    (
        SELECT  users.*,
                (SELECT count(*) FROM bookshelf WHERE bookshelf.user_id = users.ID) as titles,
                (SELECT count(*) FROM book_reviews WHERE book_reviews.user_id = users.ID) as bookreviews,
                (SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID) as recipereviews
        FROM    users   
        ) q

Answer 2

有两个选项：

选项1:丑陋是地狱，而且可能很慢(取决于查询缓存)：

SELECT users.*,
   (SELECT count(*) FROM bookshelf WHERE bookshelf.user_id = users.ID) as titles,
   (SELECT count(*) FROM book_reviews WHERE book_reviews.user_id = users.ID) as bookreviews,
   (SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID) as recipereviews
   (SELECT count(*) FROM book_reviews WHERE book_reviews.user_id = users.ID) + (SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID) as reviewtotals
FROM users   

选项2:将结果保存到临时表中，然后查询该表

也许这能行得通(还没试过)

SELECT *, bookreviews+recipereviews as reviewtotals FROM
(SELECT users.*,
   (SELECT count(*) FROM bookshelf WHERE bookshelf.user_id = users.ID) as titles,
   (SELECT count(*) FROM book_reviews WHERE book_reviews.user_id = users.ID) as bookreviews,
   (SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID) as recipereviews
FROM users) u  

Answer 3

如果你想安全快速，你可以这样做：

SELECT users.*
,      titles.num                            titles
,      book_reviews.num                      book_reviews
,      recipe_reviews.num                    recipe_reviews
,      book_reviews.num + recipe_reviews.num total_reviews
FROM      users   
LEFT JOIN (
          SELECT   user_ID, count(*) AS num
          FROM     bookshelf
          GROUP BY user_ID
          ) as titles
ON        users.ID = titles.user_ID
LEFT JOIN (
          SELECT   user_ID, count(*) AS num
          FROM     book_reviews
          GROUP BY user_ID
          ) as book_reviews
ON        users.ID = reviews.user_ID
LEFT JOIN (
          SELECT   user_ID, count(*) AS num
          FROM     recipe_reviews
          GROUP BY user_ID
          ) as recipe_reviews
ON        users.ID = recipes.user_ID

如果您想坚持使用SELECT列表中的子查询，并且希望它是安全的，那么可以看看Quassnoi的解决方案。

如果你想活得有点危险，但仍然想要一个快速的结果，你可以使用用户定义的变量。我预测在这种特殊情况下它将是安全的：

SELECT users.*,
       (SELECT count(*) FROM bookshelf WHERE bookshelf.user_id = users.ID) as titles,
       @bookreviews:=(
           SELECT count(*) 
           FROM book_reviews 
           WHERE book_reviews.user_id = users.ID
       ) as bookreviews,
       @recipereviews:=(
           SELECT count(*) 
           FROM recipe_reviews 
           WHERE recipe_reviews.user_id = users.ID
       ) as recipereviews,
       @bookreviews + @recipereviews as total_reviews
FROM users