我正在使用Codeigniter和MySQL。 我有两个表名作为information和review 。
这里我附上了我的information表的图片截图。
这是我的review表
这是我的info_img表
现在,我希望按照mytable information id获得完全没有review 。
在这里,我写了这样的查询:
SELECT info.*, info_img.name AS image, COUNT(rev.review_id) AS total FROM information AS info LEFT JOIN info_img ON info.information_id = info_img.information_id LEFT JOIN review AS rev ON rev.information_id = info.information_id WHERE info.status = 1 AND FIND_IN_SET('3', info.category) GROUP BY rev.information_id ORDER BY info.information_id ASC LIMIT 0 , 3我得到18条评论information_id = 8 ,因为information_id = 8这是错误的。 而不是它我想将review_id information_id = 8评论数量计为6。
I am working with Codeigniter and MySQL. I have two table name as information and review.
Here I have attached image screenshot for my information table.
This is my review table
and this is my info_img table
Now, I want to get total no of review by information id as per mytable.
Here, I have written query like this :
SELECT info.*, info_img.name AS image, COUNT(rev.review_id) AS total FROM information AS info LEFT JOIN info_img ON info.information_id = info_img.information_id LEFT JOIN review AS rev ON rev.information_id = info.information_id WHERE info.status = 1 AND FIND_IN_SET('3', info.category) GROUP BY rev.information_id ORDER BY info.information_id ASC LIMIT 0 , 3I got 18 review count for information_id = 8 that is wrong. Instead of it I want review count as 6 for information_id = 8.
最满意答案
我认为你所要做的就是增加你的数量......:
select info.*,info_img.name as image,count(distinct rev.review_id) as total from information as info left join info_img ON info.information_id = info_img.information_id left join review as rev ON rev.information_id = info.information_id WHERE info.status =1 AND FIND_IN_SET('3', info.category) GROUP BY rev.information_id order by info.information_id Asc LIMIT 0,3它发生的原因可能是因为信息表将结果相乘(看起来是3)所以每个review_id将被计数3次,因此 - 计数不同
I think all you have to do is add distinct to your count.. :
select info.*,info_img.name as image,count(distinct rev.review_id) as total from information as info left join info_img ON info.information_id = info_img.information_id left join review as rev ON rev.information_id = info.information_id WHERE info.status =1 AND FIND_IN_SET('3', info.category) GROUP BY rev.information_id order by info.information_id Asc LIMIT 0,3The reason its happening is probably because information table is multiplying the results(by 3 as it seems) so each review_id will be counted 3 times, therefor - count distinct
更多推荐
发布评论