-- select p.pname,p.pcode,s.saletime,count(s.aid) as total from products as p
--
-- left join sales_detail as s on (s.pcode=p.pcode)
--
-- where s.saletime in ('2008-09-23','2008-09-24')
--
select p.pname,p.pcode,s.saletime,count(s.aid) as total from products as p
left join sales_detail as s on ((s.pcode=p.pcode) and s.saletime in ('2008-09-23','2008-09-24'))
--
-- left join sales_detail as s on (s.pcode=p.pcode)
--
-- where s.saletime in ('2008-09-23','2008-09-24')
--
-- group by p.pcode order by total desc,p.pid asc
select p.pname,p.pcode,s.saletime,count(s.aid) as total from products as p
left join sales_detail as s on ((s.pcode=p.pcode) and s.saletime in ('2008-09-23','2008-09-24'))
group by p.pcode order by total desc,p.pid asc
对于JOIN参与的表的关联操作,如果需要不满足连接条件的行也在我们的查询范围内的话,我们就必需把连接条件放在ON后面,而不能放在WHERE后面,如果我们把连接条件放在了WHERE后面,那么所有的LEFT,RIGHT,等这些操作将不起任何作用,对于这种情况,它的效果就完全等同于INNER连接。对于那些不影响选择行的条件,放在ON或者WHERE后面就可以。
http://www.2cto.com/database/201111/112033.html