首先情景是这样的 一个订单表orderlist o 一个文章表article a 一个教师表teacher t
他们的关系是 o.course=a.id a.author=t.id
错误也是一开始想的的格式 反复试以为是哪里的字段或者是条件错了
select o.unionid,o.course,o.courseType,o.status,a.id,a.cat_id,a.title,a.description,a.price,a.add_time,a.file_url,a.author,a.chapter_sum,t.tname,t.tid
from orderlist as o
left join article as a
left join teacher as t
on o.course=a.id and a.author=t.tid
where o.status = '1' and o.courseType='book' and o.unionid='$oETPTs1vnCh8na5kMvciVw3fvDA8'"
正确的格式应该是下面的样子
select o.id,o.course,o.courseType,o.unionid,
a.id,a.cat_id,a.title,a.description,a.price,a.add_time,a.file_url,a.author,a.chapter_sum,t.tid,t.tname
from orderlist as o
left join article as a ON o.course=a.id
left join teacher as t ON a.author=t.tid
where o.status = '1' and o.courseType='book' and o.unionid = 'oETPTs1vnCh8na5kMvciVw3fvDA8';
扩展:5表联查
select o.id,o.course,o.courseType,o.unionid,a.id,a.cat_id,a.title,a.description,a.price,a.add_time,a.file_url,a.author,a.chapter_sum,t.tid,t.tname,count(DISTINCT m.id) as mtatol,count(DISTINCT l.id) as ltatol
from orderlist as o
left join article as a ON o.course=a.id //这的on的用法上面已经讲过了
left join teacher as t ON a.author=t.tid
left join comment as m ON a.id = m.article_id
left join collect as l ON a.id = l.aid
where o.status = '1' and o.courseType='book' and o.unionid='oETPTs1vnCh8na5kMvciVw3fvDA8' group by o.course
(没有加distinct)之前这里来看看count()的用法 用count() 要和group by 配合使用 group by要根据最左边的表的id分组 这样才能保证每条orderlist中的数据都能搜索出来,也才能保证后面的数据不出现缺失。结果数据都能搜索到 而且数据条数没有缺失,但是数据不正确,太多
加上distinct排重之后 数据显示正常
所以 统计要注意groupy by 的分组 也要注意 count()中要加distinct
where > group by >having> order by