Question

我试图把最后10个评级高于4个的行文清单拉开。现在是通过从第一个表格中选取,然后核对其高于4的平均数,然后在变数中加起来,如果不是这样的话。特别是在有1 000多个牢房的情况下,这是这样做的可怕方式。

这里有两个问题:

SELECT * FROM `CLMS_reviews` WHERE id = 465 ORDER BY date DESC

SELECT 
rc.name, rr.rating
FROM `CLMS_reviews_ratings` rr  
LEFT JOIN `CLMS_reviews_categories` rc ON rc.cat_id = rr.cat_id  AND rc.website_id = 465
WHERE rr.review_id = " . $row[ id ] . "
GROUP BY rc.name

我怎么能把这个问题合并为一个问题? 我试图这样做,但因以下原因使小组无法使用:

SELECT 
rr.review_id
FROM `CLMS_reviews` r
LEFT JOIN `CLMS_reviews_ratings` rr ON rr.review_id = r.id
WHERE r.website_id = 465 AND AVG(rr.rating) > 4
GROUP BY rr.review_id LIMIT 10

偏见摘自:

SELECT review_id FROM `CLMS_reviews_ratings`
WHERE review_id IN ((SELECT id FROM `CLMS_reviews` WHERE website_id = 465))
GROUP BY review_id
HAVING avg(rating) > 4
LIMIT 10

Answer 1

不能对此进行测试,但可能这样做:

SELECT rr.review_id
  FROM `CLMS_reviews` r
  LEFT JOIN `CLMS_reviews_ratings` rr ON rr.review_id = r.id
 WHERE r.website_id = 465
 GROUP BY rr.review_id
HAVING avg(rr.rating) > 4
 LIMIT 10;

如果您需要按总体职能进行过滤,HAVING是您的附录。

Answer 2

我认为,你需要使用<代码>HAVING syntax。与此类似:

SELECT 
rr.review_id
FROM `CLMS_reviews` r
LEFT JOIN `CLMS_reviews_ratings` rr ON rr.review_id = r.id
WHERE r.website_id = 465
GROUP BY rr.review_id 
HAVING AVG(rr.rating) > 4
LIMIT 10

友情链接