我有評級表，其中對用戶給出的每個產品進行評級，我正在檢索所有評級記錄，但同時我想獲得基于每個產品的平均評級，但我無法獲得輸出詢問 ：$this->db->select('ratings.*');$this->db->select('select AVG(ratings) as avg_rating from ratings group by product_id');$this->db->from('ratings');$this->db->join('users','users.id=ratings.user_id');$this->db->get()->result();評級表id user_id product_id rating 1 4 1 4 2 5 2 43 6 1 24 7 4 4預期輸出：id user_id product_id rating avg rating1 4 1 4 3 2 5 2 4 43 6 1 2 34 7 4 4 4
1 回答
夢里花落0921
TA貢獻1772條經驗 獲得超6個贊
使用左連接和選擇平均值從表ratings獲取數據。
Codeigniter 的join () 函數允許您編寫選擇部分而不是表名，但您需要將其放在括號中：
$this->db->select('t1.*, t2.avg_rating, t3.*');
$this->db->from('ratings t1');
$this->db->join('
(select product_id, avg(rating) as avg_rating
from ratings
group by product_id) t2','t2.product_id=t1.product_id','left'
);
$this->db->join('users t3','t3.id=t1.user_id','left');
$this->group_by('t1.userid')
$this->db->get()->result();
生成：
SELECT t1.*, t2.avg_rating, t3.*
FROM ratings t1
left join
(select product_id, avg(rating) as avg_rating from ratings group by product_id) t2
on t2.product_id=t1.product_id
left join users t3
on t1.user_id = t3.id
group by t1.user_id
并按照您的預期輸出。
- 1 回答
- 0 關注
- 111 瀏覽
添加回答
舉報
0/150
提交
取消