2017.12.05 数据库联表查询

select a.question_id,count(1) as total
from (answer a LEFT JOIN likes b on a.id = b.entity_id) RIGHT JOIN statistic c on a.question_id = c.id
where a.status = 1 and b.entity_type = 2 and b.status =1 and c.status = 1 group by a.question_id order by c.data_value desc limit 10

1.count(1) 不计算null的
2.select * from (…) 临时表名 where ….
3.order by … desc 默认升序 desc降序
4.limit 10 前10个
5.联表 left/right join
以left join为例 以左表为准 右表中没有的就在左表中置空
http://blog.csdn.net/mr_tim/article/details/51135377
on a.id = b.entity_id 关联条件 相同名称可以用USING(id)。

6.先按照关联条件 (on) 然后在按照where找。