三表查询(文章,类别,评论表联合查询)

1  SELECT a.art_id,a.hits,a.title,cat.cate_name,a.addtime,a.art_desc,a.thumb,COUNT(com.art_id) as comment 

from bg_article as a LEFT JOIN bg_category as cat on a.cate_id=cat.cate_id LEFT JOIN bg_comment as com on a.art_id=com.art_id GROUP BY a.art_id;

其中文章的基本信息都有,但是分类是bg_category分类表的,评论个数是bg_comment中的,sql语句

实现过程是先写bg_article 和 bg_category表的关系和所需要的数据,再联合bg_comment表查询其中的评论数,

以上的例子说明了写sql语句一步一步写,万丈高楼从低起。

 

 

以下是根据id和分类的三表查询:

SELECT a.art_id,a.hits,a.title,a.is_recommend,cat.cate_name,a.addtime,a.art_desc,a.thumb,COUNT(com.art_id) 
as comment from bg_article   as a  LEFT JOIN bg_category  
as cat on a.cate_id=cat.cate_id LEFT JOIN bg_comment 
as com on a.art_id=com.art_id WHERE  a.is_recommend='1'
GROUP BY a.art_id   ORDER BY a.addtime desc

 

posted @ 2017-10-06 20:53  QinXiao.Shou  阅读(1368)  评论(0编辑  收藏  举报