平均评级，然后使用代码点火器连接2个表

Question

我需要一些帮助，我有两个表，像这个

table a                      table b
+-------+---------+       +---+--------+--------+
| no    |   name  |       | NO| cafe   | rating | 
+-------+---------+       +---+--------+--------+
|   1   | cafe a  |       | 1 | cafe a |   5    |
|   2   | cafe b  |       | 2 | cafe a |   4    |
|   3   | cafe c  |       | 3 | cafe b |   4    |
| 4 | cafe b |   3    |
| 5 | cafe a |   2    |

我想要的结果

+-------+---------+-------+
| no    |   name  |rating | 
+-------+---------+-------+
|   1   | cafe a  | 4.5   | 
|   2   | cafe b  | 3.5   | 
|   3   | cafe c  |   2   | 

有人能告诉我如何使用codeigniter解决它吗，或者给模型一个参考。感谢

Answer 1

您似乎想要一个join和聚合：

select a.*, b.rating
from (select name, avg(b.rating) as rating
from b
group by name
) b join
a
on a.name = b.name;

请注意，有些数据库执行整数平均，因此您可能需要avg(b.rating * 1.0)。