gpt4 book ai didi

mysql - 如何优化包含两个相同子查询的查询(聚合函数除外)?

转载 作者:行者123 更新时间:2023-11-29 04:43:04 24 4
gpt4 key购买 nike

我正在尝试编写一个简单的查询,生成按以下两项排名的图书列表:最新评论和评论总数。为了计算每本书的最近评论日期,我使用了一个子查询和 MIN 聚合函数。为了计算每本书的评论总数,我使用了完全相同的子查询,但使用了 COUNT 聚合函数。

这看起来效率不高。有没有一种方法可以将这些子查询合并到一个调用中,并且仍然可以访问查询的 HAVING 和 ORDER BY 子句中的两个聚合值?

这是伪查询:

SELECT DISTINCT
(SELECT MIN(review.pub_date)
FROM review
WHERE book.id = review.book_id
AND ...
AND ...
AND ...
) AS most_recent_date,
(SELECT COUNT(review.pub_date)
FROM review
WHERE book.id = review.book_id
AND ...
AND ...
AND ...
) AS review_count,
book.id, book.pub_date, book.title, book.author
FROM book
INNER JOIN review
ON (review.book_id = book.id)
WHERE (
...
)
HAVING (most_recent_date > '$DATE')
ORDER BY review_count DESC

我尝试将它们合并为一个并将聚合函数从子查询移动到 HAVING 和 ORDER BY 子句中,但我无法通过这种方式检索任何结果。

提前致谢。

最佳答案

我不认为你需要子查询来做到这一点:

select 
book.id, book.pub_date, book.title, book.author,
min(review.pub_date) as most_recent_date, -- are you sure you want "min"? ("max" makes more sense to me)
count(review.pub_date) as review_count
from book
left join review on book.id = review.book_id
where ... -- Define here any conditions, including those inside the subquery
group by book.id
having most_recent_date > ?
order by review_count desc

优化它的其他方法是使用您想要的结果创建临时表,然后将结果拉到您的查询中。

关于mysql - 如何优化包含两个相同子查询的查询(聚合函数除外)?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/24471035/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com