I am running a MySQL query to rank my site's users according to the number of book reviews and recipe reviews they have contributed. After initial issues with a multiple JOIN query, I've switched to a series of subqueries, which is much, much faster. However, although I can extract the numbers of reviews from each member, I can't figure out how to add them together so I can sort by the total number.
Here's the current query:
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
I need to add together bookreviews and reciperevie开发者_如何学JAVAws to get 'reviewtotals'. MySQL won't allow you to use simple syntax to do calculations on aliases, but I presume there's another way to do this??
Wrap it into a subquery:
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
If you want to be safe and fast, do it like this:
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
If you want to stick to the subqueries in the SELECT
list, and want it to be safe, take a look at Quassnoi's solution.
If you like to live a bit dangersouly and still want a fast result, you can use user-defined variables. I predict it will be safe in this very particular case:
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
Two options:
Option 1:Ugly is hell, and probably slow (depends on the query cache):
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
Option 2: Save the results to a temporary table and then query this table
Perhaps this will work (haven't tried it)
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
You tried the following?
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) +
(SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID)) as reviewtotal
FROM users
精彩评论