我有評(píng)級(jí)表，其中對(duì)用戶給出的每個(gè)產(chǎn)品進(jìn)行評(píng)級(jí)，我正在檢索所有評(píng)級(jí)記錄，但同時(shí)我想獲得基于每個(gè)產(chǎn)品的平均評(píng)級(jí)，但我無法獲得輸出詢問 ：$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();評(píng)級(jí)表id user_id product_id rating 1 4 1 4 2 5 2 43 6 1 24 7 4 4預(yù)期輸出：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 回答
夢(mèng)里花落0921
TA貢獻(xiàn)1772條經(jīng)驗(yàn) 獲得超6個(gè)贊
使用左連接和選擇平均值從表ratings獲取數(shù)據(jù)。
Codeigniter 的join () 函數(shù)允許您編寫選擇部分而不是表名，但您需要將其放在括號(hào)中：
$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
并按照您的預(yù)期輸出。
- 1 回答
- 0 關(guān)注
- 123 瀏覽
添加回答
舉報(bào)
0/150
提交
取消