mysql

MySQL关联left join 条件on与where不同

以下的文章主要讲述的是MySQL关联left join 条件on与where 条件的不同之处，我们现在有两个表，即商品表(products)与sales_detail(销售记录表)。我们主要是通过这两个表来对MySQL关联left join 条件on与where 条件的不同之处进行讲述。

products:

pid pname pcode

1 商品1 AC90

2 商品2 DE78

3 商品3 XXXX

1
2
3
4
5
6
sales_detail:    
aid pcode saletime    
1 AC90 2008-09-22    
2 DE78 2008-09-22    
3 AC90 2008-09-23    
4 AC90 2008-09-24

某个时间段内的销售量来排行，比如我想统计23－24号这两天的销售数量并排行。（注：DE78这个商品在这两天没有销售，但是也要显示出来，只是数量为0）。

MySQL关联left join条件on和where条件的区别表的结构 products

1
2
3
4
5
6
CREATE TABLE `products` (    
`pid` int(3) NOT NULL auto_increment,    
`pname` varchar(20) NOT NULL,    
`pcode` varchar(20) NOT NULL,    
PRIMARY KEY (`pid`)    
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

导入表中的数据 products

1
2
3
4
INSERT INTO `products` (`pid`, `pname`, `pcode`) VALUES   
(1, '商品1', 'AC90'),    
(2, '商品2', 'DE78'),    
(3, '商品3', 'XXXX');

表的结构 sales_detail

1
2
3
4
5
6
CREATE TABLE `sales_detail` (    
`aid` int(3) NOT NULL auto_increment,    
`pcode` varchar(20) NOT NULL,    
`saletime` date NOT NULL,    
PRIMARY KEY (`aid`)    
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

表中的数据 sales_detail

1
2
3
4
5
INSERT INTO `sales_detail` (`aid`, `pcode`, `saletime`) VALUES   
(1, 'AC90', '2008-09-22'),    
(2, 'DE78', '2008-09-22'),    
(3, 'AC90', '2008-09-23'),    
(4, 'AC90', '2008-09-24');

MySQL关联left join条件on和where条件的区别区别两条sql语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

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')     
group by p.pcode   
order by total desc,p.pid asc  
  
+-------+-------+------------+  
| pname | pcode | saletime   |  
+-------+-------+------------+  
| 商品1 | AC90  | 2008-09-23 |  
+-------+-------+------------+  
  
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  
  
+-------+-------+------------+-------+  
| pname | pcode | saletime   | total |  
+-------+-------+------------+-------+  
| 商品1 | AC90  | 2008-09-23 |     2 |  
| 商品2 | DE78  | NULL       |     0 |  
| 商品3 | XXXX  | NULL       |     0 |  
+-------+-------+------------+-------+

心得：on中的条件关联，一表数据不满足条件时会显示空值。where则输出两表完全满足条件数据。