gpt4 book ai didi

mysql - 组合查询

转载 作者:行者123 更新时间:2023-11-29 03:10:36 25 4
gpt4 key购买 nike

尝试节省一个步骤并组合这两个查询,但不确定如何最好地进行。基本上,第一个查询是获取我们系统中的评论列表,第二个查询是查看是否已发送通知(如果返回结果则表示已发送)。

获取评论:

SELECT r.*, 
coalesce( ( SELECT AVG(rr.rating)
FROM `" . DBTABLEPREFIX . "reviews_ratings` rr
WHERE rr.review_id = r.id
)
, ''
) AS rating,
FROM `" . DBTABLEPREFIX . "reviews` r
WHERE BELOW SELECT IS 0 RESULTS

检查通知是否存在(是的,我知道这本身不起作用):

SELECT rn.* 
FROM `" . DBTABLEPREFIX . "reviews_notifications` rn
WHERE rn.website_id = r.website_id
AND rn.post_id = r.post_id

关于如何正确执行此操作的任何想法?另外,如果您想知道为什么我们不能检查通知表上的 r.id,这是因为每天都会用新数据清除评论和评论评级表。稍后可能会重写该过程,以不杀死而仅添加新项目。

最佳答案

您可以使用相关子查询,使用 NOT EXISTS:

SELECT r.*, 
coalesce( ( SELECT AVG(rr.rating)
FROM `" . DBTABLEPREFIX . "reviews_ratings` rr
WHERE rr.review_id = r.id
)
, ''
) AS rating,
FROM `" . DBTABLEPREFIX . "reviews` r
WHERE NOT EXISTS
( SELECT rn.*
FROM `" . DBTABLEPREFIX . "reviews_notifications` rn
WHERE rn.website_id = r.website_id
AND rn.post_id = r.post_id
)

其他子查询可以移到FROM部分:

SELECT 
r.*,
coalesce( rr.rating, '' ) AS rating,
FROM `" . DBTABLEPREFIX . "reviews` r
LEFT JOIN
( SELECT
rr.review_id,
AVG(rr.rating) AS rating
FROM `" . DBTABLEPREFIX . "reviews_ratings` rr
GROUP BY rr.review_id
) AS rr
ON rr.review_id = r.id
WHERE NOT EXISTS
( SELECT rn.*
FROM `" . DBTABLEPREFIX . "reviews_notifications` rn
WHERE rn.website_id = r.website_id
AND rn.post_id = r.post_id
)

关于mysql - 组合查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9034891/

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