select a.*,b.sname,c.num,d.count from goods a
left join store b on b.sid=a.sid
left join type t1 on t1.tid=a.tid left join type t2 on t1.tparentid=t2.tid left join type t3 on t2.tparentid=t3.tid
left join
(select sum(c.num) num, c.gid from ordersdetail c
left join orders d on c.oid=d.oid
where d.OSTATUS<>6 and d.OSTATUS<>-1 and d.OSTATUS<>5 group by c.gid) c on c.gid=a.gid
left join
(select count(*) count,gid from discuss) d on d.gid=a.gid
where b.sstatus=1 and a.gstatus=1 and t1.tparentid is not null and t2.tparentid is not null order by c.num desc
这个SQL中用到了goods、store、type、ordersdetail、orders、discuss六个表
首先要做到一步步的关联,即left join.....on......
type表中是一个三级联关系的表自关联
sum(num)是根据group by 的gid来求num的和