mysql - 其他表中的 AVG 连接结果

Question

我有一个avg结果查询。在另一个表中，我有该结果的 avg结果文本。

现在这是我的查询：

select round(avg(breed_ratings.rating)) as result, breed_ratings.score_name, count(*) as total, breeds.name_en
from breed_ratings
inner join breeds on breeds.id = breed_ratings.breed_id 
where breeds.id = 188
group by score_name, breeds.name_en

rating_result 表如下所示：

id
rating
result_text

我怎样才能得到result_text这个查询？

请帮帮我。

- 编辑

我需要result从下面的图像中获取文本。

所以我有另一个存储它的表我需要得到与评级匹配的result_nl：

期望的结果（如果评级为 5）：

result: 5
score_name: ADULT_FRIENDLY
total: 117
name_en: American Staffordshire Terrier
result_nl: I am extremely dominant

标签： mysqlsqlsubqueryinner-join

Answer 1

我认为您希望join将平均值设为第一个评分，rating_results其中平均值大于那里列出的评分。如果是这样：

select br.*, rr.result_text
from (select round(avg(br.rating)) as result, br.score_name, count(*) as total, b.name_en
      from breed_ratings br join
           breeds b
           on b.id = br.breed_id 
      where b.id = 188
      group by br.score_name, b.name_en
     ) br left join
     (select rr.*, lead(rr.rating) over (order by rr.rating) as next_rating
      from rating_result rr
     ) rr
     on br.result >= rr.rating and
        (br.result < rr.next_rating or rr.next_rating is null)