MySQL多个SUM返回相同的值 [英] MySQL multiple SUM returning same value
本文介绍了MySQL多个SUM返回相同的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个花哨的东西"(就像帖子一样)，当我从数据库中选择它时，我会总结评论，喜欢的数量以及当前用户是否喜欢.
I have a "bloomp" (like a post), when I select it from the database, I SUM the number of comments, likes and if the current user liked.
问题在于喜欢和评论的数量.例如:
The problem is with the number of likes and comments. For example:
如果Bloomp具有3个赞和1条评论，则选择返回具有3个赞和3条评论的产品，反之亦然.
If the bloomp has 3 likes and 1 comment, the select return that it has 3 likes and 3 comments, and vice-versa.
这是我的查询:
SELECT `bloomps`.`id` AS id,
`bloomps`.`text` AS text,
`bloomps`.`date` AS date,
`images`.`id` AS image_id,
`videos`.`id` AS video_id,
`videos`.`provider` AS video_provider,
`users`.`name` AS author_name,
`users`.`picture` AS author_picture,
SUM(CASE WHEN likes.bloomp_id IS NOT NULL THEN 1 ELSE 0 END) AS likes_number,
SUM(CASE WHEN likes.bloomp_id IS NOT NULL AND likes.author_id = 1 THEN 1 ELSE 0 END) AS liked,
SUM(CASE WHEN comments.bloomp_id IS NOT NULL THEN 1 ELSE 0 END) AS comments_number
FROM (`bloomps`)
INNER JOIN `users` ON `users`.`id` = `bloomps`.`author_id`
LEFT JOIN `likes` ON `likes`.`bloomp_id` = `bloomps`.`id`
LEFT JOIN `comments` ON `comments`.`bloomp_id` = `bloomps`.`id`
LEFT JOIN `images` ON `images`.`bloomp_id` = `bloomps`.`id`
LEFT JOIN `videos` ON `videos`.`bloomp_id` = `bloomps`.`id`
GROUP BY `bloomps`.`id`
ORDER BY `bloomps`.`id` ASC
LIMIT 10
此查询:
SELECT `bloomps`.`id` AS id,
`bloomps`.`text` AS text,
`bloomps`.`date` AS date,
`users`.`name` AS author_name,
`users`.`picture` AS author_picture,
SUM(CASE WHEN likes.bloomp_id IS NOT NULL THEN 1 ELSE 0 END) AS likes_number,
SUM(CASE WHEN likes.bloomp_id IS NOT NULL AND likes.author_id = 1 THEN 1 ELSE 0 END) AS liked,
SUM(CASE WHEN comments.bloomp_id IS NOT NULL THEN 1 ELSE 0 END) AS comments_number
FROM (`bloomps`)
INNER JOIN `users` ON `users`.`id` = `bloomps`.`author_id`
LEFT JOIN `likes` ON `likes`.`bloomp_id` = `bloomps`.`id`
LEFT JOIN `comments` ON `comments`.`bloomp_id` = `bloomps`.`id`
GROUP BY `bloomps`.`id`
ORDER BY `bloomps`.`id` ASC
LIMIT 10
正在返回:
Array
(
[0] => Array
(
[id] => 1
[text] => All these years I've been feeling like I was growing into myself. Finally, I feel grown.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] => yfg8CMWeUAU
[video_provider] => youtube
[author_name] => Claudius Ibn
[author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
[likes_number] => 10
[liked] => 5
[comments_number] => 10
)
[1] => Array
(
[id] => 2
[text] => Reveal not every secret you have to a friend, for how can you tell but that friend may hereafter become an enemy. And bring not all mischief you are able to upon an enemy, for he may one day become your friend.
[date] => 0000-00-00 00:00:00
[image_id] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
[video_id] =>
[video_provider] =>
[author_name] => Elza Virginia
[author_picture] => 77de68daecd823babbb58edb1c8e14d7106e83bb.jpg
[likes_number] => 6
[liked] => 6
[comments_number] => 6
)
[2] => Array
(
[id] => 3
[text] => To me, old age is always 15 years older than I am.
[date] => 0000-00-00 00:00:00
[image_id] => 41025046575248278_WzB3wKD0_f.jpg
[video_id] =>
[video_provider] =>
[author_name] => Elza Virginia
[author_picture] => 77de68daecd823babbb58edb1c8e14d7106e83bb.jpg
[likes_number] => 1
[liked] => 1
[comments_number] => 0
)
[3] => Array
(
[id] => 4
[text] => Accept the pain, cherish the joys, resolve the regrets; then can come the best of benedictions - 'If I had my life to live over, I'd do it all the same.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] =>
[video_provider] =>
[author_name] => Claudio Cardozo
[author_picture] => da4b9237bacccdf19c0760cab7aec4a8359010b0.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 0
)
[4] => Array
(
[id] => 5
[text] => Be master of your petty annoyances and conserve your energies for the big, worthwhile things. It isn't the mountain ahead that wears you out - it's the grain of sand in your shoe.
[date] => 0000-00-00 00:00:00
[image_id] => 4889470_700b.jpg
[video_id] =>
[video_provider] =>
[author_name] => Claudius Ibn
[author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
[likes_number] => 1
[liked] => 1
[comments_number] => 0
)
[5] => Array
(
[id] => 6
[text] => I think we're having fun. I think our customers really like our products. And we're always trying to do better.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] =>
[video_provider] =>
[author_name] => Claudius Ibn
[author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 0
)
[6] => Array
(
[id] => 7
[text] => [Addiction's] not about placating the bad dog - it's about feeding the good dog. You still have to feed the bad dog, but only enough so that the ASPCA doesn't bring you up on charges.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] =>
[video_provider] =>
[author_name] => Claudius Ibn
[author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 0
)
[7] => Array
(
[id] => 8
[text] => I've been on a diet for two weeks and all I've lost is two weeks.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] =>
[video_provider] =>
[author_name] => Claudio Cardozo
[author_picture] => da4b9237bacccdf19c0760cab7aec4a8359010b0.jpg
[likes_number] => 1
[liked] => 1
[comments_number] => 0
)
[8] => Array
(
[id] => 9
[text] => A book burrows into your life in a very profound way because the experience of reading is not passive.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] =>
[video_provider] =>
[author_name] => Elza Virginia
[author_picture] => 77de68daecd823babbb58edb1c8e14d7106e83bb.jpg
[likes_number] => 1
[liked] => 1
[comments_number] => 0
)
[9] => Array
(
[id] => 10
[text] => Touch your customer, and you're halfway there.
[date] => 0000-00-00 00:00:00
[image_id] => 4878040_460s_v1.jpg
[video_id] =>
[video_provider] =>
[author_name] => Claudius Ibn
[author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 0
)
[10] => Array
(
[id] => 11
[text] => I know starting careers in troubled times is a challenge, but it is also a privilege. Because it's moments like these that force us to try harder, dig deeper and to discover gifts we never knew we had. To find the greatness that lies within each of us. So don't ever shy away from that endeavor. Don't stop adding to your body of work. I can promise that you will be the better for that continued effort as will be this nation that we all love.
[date] => 0000-00-00 00:00:00
[image_id] => 20547742019404735_U0zJkj83_f.jpg
[video_id] =>
[video_provider] =>
[author_name] => Elza Virginia
[author_picture] => 77de68daecd823babbb58edb1c8e14d7106e83bb.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 0
)
[11] => Array
(
[id] => 12
[text] => Misery no longer loves company. Nowadays it insists on it.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] =>
[video_provider] =>
[author_name] => Elza Virginia
[author_picture] => 77de68daecd823babbb58edb1c8e14d7106e83bb.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 1
)
[12] => Array
(
[id] => 13
[text] => A good novel tells us the truth about its hero; but a bad novel tells us the truth about its author.
[date] => 0000-00-00 00:00:00
[image_id] => 4887752_460s.jpg
[video_id] =>
[video_provider] =>
[author_name] => Elza Virginia
[author_picture] => 77de68daecd823babbb58edb1c8e14d7106e83bb.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 0
)
[13] => Array
(
[id] => 14
[text] => Acting is the most minor of gifts and not a very high-class way to earn a living. After all, Shirley Temple could do it at the age of four.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] =>
[video_provider] =>
[author_name] => Claudius Ibn
[author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 0
)
[14] => Array
(
[id] => 15
[text] => The time to repair the roof is when the sun is shining.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] =>
[video_provider] =>
[author_name] => Claudius Ibn
[author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 0
)
[15] => Array
(
[id] => 16
[text] => I am doomed to an eternity of compulsive work. No set goal achieved satisfies. Success only breeds a new goal. The golden apple devoured has seeds. It is endless.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] => 5431060
[video_provider] => vimeo
[author_name] => Claudio Cardozo
[author_picture] => da4b9237bacccdf19c0760cab7aec4a8359010b0.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 0
)
[16] => Array
(
[id] => 17
[text] => In every American there is an air of incorrigible innocence, which seems to conceal a diabolical cunning.
[date] => 0000-00-00 00:00:00
[image_id] => image18.png
[video_id] =>
[video_provider] =>
[author_name] => Claudius Ibn
[author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
[likes_number] => 1
[liked] => 1
[comments_number] => 0
)
[17] => Array
(
[id] => 18
[text] => Everything should be made as simple as possible, but not one bit simpler.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] =>
[video_provider] =>
[author_name] => Claudius Ibn
[author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 0
)
[18] => Array
(
[id] => 19
[text] => When I only begin to read, I forget I'm on this world. It lifts me on wings with high thoughts.
[date] => 0000-00-00 00:00:00
[image_id] =>
[video_id] =>
[video_provider] =>
[author_name] => Claudio Cardozo
[author_picture] => da4b9237bacccdf19c0760cab7aec4a8359010b0.jpg
[likes_number] => 0
[liked] => 0
[comments_number] => 0
)
)
推荐答案
您已将多个表连接到主(bloomps)表，所有这些表都具有一对多的关系.结果是微型笛卡尔联接，并且在分组时计数错误.
You have joined more than one tables to the main (bloomps) table and all these have 1-to-many relationship with it. The result is a mini-cartesian join and wrong counts when grouping.
您需要将每个子查询分别分组，然后将它们连接到基表-而不在此分组.试试这个:
You need to group each one separately in subqueries and then join these to the base table - and no grouping there. Try this:
SELECT
b.id
, b.* -- whatever column from `bloomps`
, COALESCE(l.cnt,0) AS likes_number,
, COALESCE(l.liked,0) AS liked,
, COALESCE(c.cnt,0) AS comments_number
FROM
bloomps AS b
LEFT JOIN
( SELECT bloomp_id
, COUNT(*) AS cnt
, COUNT(CASE WHEN author_id = 1 THEN 1 END) AS liked
FROM likes
GROUP BY bloomp_id
) AS l
ON l.bloomp_id = b.id
LEFT JOIN
( SELECT bloomp_id
, COUNT(*) AS cnt
FROM comments
GROUP BY bloomp_id
) AS c
ON c.bloomp_id = b.id
-- no need for GROUP BY
ORDER BY id ASC
LIMIT 10 ;
如果您不想使用子查询，则可以对查询进行以下修改.测试哪个效率更高:
If you don't want subqueries, you could keep the query with the modifications below. Test which one is more efficient:
SELECT
b.id
, b.test
, b.date
, COUNT(DISTINCT l.id) AS likes_number,
, COUNT(DISTINCT CASE WHEN l.author=1 THEN l.id END) AS liked,
, COUNT(DISTINCT c.id) AS comments_number
FROM
bloomps AS b
LEFT JOIN
likes AS l
ON l.bloomp_id = b.id
LEFT JOIN
comments AS c
ON c.bloomp_id = b.id
GROUP BY b.id ASC -- MySQL non-standard syntax for
-- GROUP BY b.id
-- ORDER BY b.id ASC
LIMIT 10 ;
这篇关于MySQL多个SUM返回相同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
