[实践Ok]mysql关联left join条件on和where条件的区别及结合coalesce函数及adddate函数的用法,这儿不是介绍left join 或者right join的区别,是一个where 中的in和left 中的in在gourp by时的区别问题。
主要是对这两篇文章的一介总结:
[实践常用]Mysql时间加减ADDDATE函数用法: http://jackxiang.com/post/4935/
[实践用到]mysql coalesce的函数用法 :http://jackxiang.com/post/4934/
注意:部分来自网路上,Url:http://blog.sina.com.cn/s/blog_613212e10100hin2.html ,还必须参考下这个哥们的这篇文章:
http://hi.baidu.com/luoxiandong99/blog/item/0a0c86b5799efa1119d81ffd.html
摘录:
为什么会存在差异,这和on与where查询顺序有关。
我们知道标准查询关键字执行顺序为 from->where->group by->having->order by[ 记得不是很清楚呢]
left join 是在from范围类所以 先on条件筛选表,然后两表再做left join。
而对于where来说在left join结果再次筛选。
。。。
如下:
现有两个表,商品表(products)和sales_detail(销售记录 表)。如下:
按商品在某个时间段内的销售量来排行,比如我想统计23-24号这两天的销售数量并排行。(注:DE78这个商品在这两天没有销售,但是也要显示出来,只 是数量为0)。
-- 表的结构 `products`:
表中的数据 `products`:
表的结构 `sales_detail`:
表中的数据 `sales_detail`:
查询:
区别两条sql语句:
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 | total |
+---------+-------+------------+-------+
| 商品1 | AC90 | 2008-09-23 | 2 |
+---------+-------+------------+-------+
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则输出两表完全满足条件数据。
================================================================================================
后记:逐步引入coalesce函数及adddate函数:
(1)简单left查询:
mysql> select * from products as p left join sales_detail as s on p.pcode=s.pcode;
+-----+---------+-------+------+-------+------------+
| pid | pname | pcode | aid | pcode | saletime |
+-----+---------+-------+------+-------+------------+
| 1 | 商品1 | AC90 | 1 | AC90 | 2008-09-22 |
| 1 | 商品1 | AC90 | 3 | AC90 | 2008-09-23 |
| 1 | 商品1 | AC90 | 4 | AC90 | 2008-09-24 |
| 2 | 商品2 | DE78 | 2 | DE78 | 2008-09-22 |
| 3 | 商品3 | XXXX | NULL | NULL | NULL |
+-----+---------+-------+------+-------+------------+
5 rows in set (0.00 sec)
(2)加入时间范围:
mysql> select * from products as p left join sales_detail as s on p.pcode=s.pcode where s.saletime in ('2008-09-23','2008-09-24');
+-----+---------+-------+------+-------+------------+
| pid | pname | pcode | aid | pcode | saletime |
+-----+---------+-------+------+-------+------------+
| 1 | 商品1 | AC90 | 3 | AC90 | 2008-09-23 |
| 1 | 商品1 | AC90 | 4 | AC90 | 2008-09-24 |
+-----+---------+-------+------+-------+------------+
2 rows in set (0.00 sec)
(3)加入group by查询:
mysql> select * from products as p left join sales_detail as s on p.pcode=s.pcode where s.saletime in ('2008-09-23','2008-09-24') group by p.pcode;
+-----+---------+-------+------+-------+------------+
| pid | pname | pcode | aid | pcode | saletime |
+-----+---------+-------+------+-------+------------+
| 1 | 商品1 | AC90 | 3 | AC90 | 2008-09-23 |
+-----+---------+-------+------+-------+------------+
1 row in set (0.00 sec)
(4)加入count统计:
mysql> select *,count(s.aid) as total from products as p left join sales_detail as s on p.pcode=s.pcode where s.saletime in ('2008-09-23','2008-09-24') group by p.pcode;
+-----+---------+-------+------+-------+------------+-------+
| pid | pname | pcode | aid | pcode | saletime | total |
+-----+---------+-------+------+-------+------------+-------+
| 1 | 商品1 | AC90 | 3 | AC90 | 2008-09-23 | 2 |
+-----+---------+-------+------+-------+------------+-------+
1 row in set (0.00 sec)
(5)改变查询出现了Null值变为1,如下,(接着就是如何去掉Null):
mysql> 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 |
+---------+-------+------------+-------+
3 rows in set (0.00 sec)
(6)用函数coalesce来去掉Null的情况:
mysql> select p.pname,p.pcode,coalesce(s.saletime,'0'),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 | coalesce(s.saletime,'0') | total |
+---------+-------+--------------------------+-------+
| 商品1 | AC90 | 2008-09-23 | 2 |
| 商品2 | DE78 | 0 | 0 |
| 商品3 | XXXX | 0 | 0 |
+---------+-------+--------------------------+-------+
(7)加入Mysql的时间函数带入查询,adddate函数日期带入where中的in查询情况:
mysql> select p.pname,p.pcode,coalesce(s.saletime,'0'),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',adddate(timestamp("2008-09-23"), interval 1 day))) group by p.pcode order by total desc,p.pid asc;
+---------+-------+--------------------------+-------+
| pname | pcode | coalesce(s.saletime,'0') | total |
+---------+-------+--------------------------+-------+
| 商品1 | AC90 | 2008-09-23 | 2 |
| 商品2 | DE78 | 0 | 0 |
| 商品3 | XXXX | 0 | 0 |
+---------+-------+--------------------------+-------+
3 rows in set (0.00 sec)
主要是对这两篇文章的一介总结:
[实践常用]Mysql时间加减ADDDATE函数用法: http://jackxiang.com/post/4935/
[实践用到]mysql coalesce的函数用法 :http://jackxiang.com/post/4934/
注意:部分来自网路上,Url:http://blog.sina.com.cn/s/blog_613212e10100hin2.html ,还必须参考下这个哥们的这篇文章:
http://hi.baidu.com/luoxiandong99/blog/item/0a0c86b5799efa1119d81ffd.html
摘录:
为什么会存在差异,这和on与where查询顺序有关。
我们知道标准查询关键字执行顺序为 from->where->group by->having->order by[ 记得不是很清楚呢]
left join 是在from范围类所以 先on条件筛选表,然后两表再做left join。
而对于where来说在left join结果再次筛选。
。。。
如下:
现有两个表,商品表(products)和sales_detail(销售记录 表)。如下:
按商品在某个时间段内的销售量来排行,比如我想统计23-24号这两天的销售数量并排行。(注:DE78这个商品在这两天没有销售,但是也要显示出来,只 是数量为0)。
-- 表的结构 `products`:
表中的数据 `products`:
表的结构 `sales_detail`:
表中的数据 `sales_detail`:
查询:
区别两条sql语句:
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 | total |
+---------+-------+------------+-------+
| 商品1 | AC90 | 2008-09-23 | 2 |
+---------+-------+------------+-------+
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则输出两表完全满足条件数据。
================================================================================================
后记:逐步引入coalesce函数及adddate函数:
(1)简单left查询:
mysql> select * from products as p left join sales_detail as s on p.pcode=s.pcode;
+-----+---------+-------+------+-------+------------+
| pid | pname | pcode | aid | pcode | saletime |
+-----+---------+-------+------+-------+------------+
| 1 | 商品1 | AC90 | 1 | AC90 | 2008-09-22 |
| 1 | 商品1 | AC90 | 3 | AC90 | 2008-09-23 |
| 1 | 商品1 | AC90 | 4 | AC90 | 2008-09-24 |
| 2 | 商品2 | DE78 | 2 | DE78 | 2008-09-22 |
| 3 | 商品3 | XXXX | NULL | NULL | NULL |
+-----+---------+-------+------+-------+------------+
5 rows in set (0.00 sec)
(2)加入时间范围:
mysql> select * from products as p left join sales_detail as s on p.pcode=s.pcode where s.saletime in ('2008-09-23','2008-09-24');
+-----+---------+-------+------+-------+------------+
| pid | pname | pcode | aid | pcode | saletime |
+-----+---------+-------+------+-------+------------+
| 1 | 商品1 | AC90 | 3 | AC90 | 2008-09-23 |
| 1 | 商品1 | AC90 | 4 | AC90 | 2008-09-24 |
+-----+---------+-------+------+-------+------------+
2 rows in set (0.00 sec)
(3)加入group by查询:
mysql> select * from products as p left join sales_detail as s on p.pcode=s.pcode where s.saletime in ('2008-09-23','2008-09-24') group by p.pcode;
+-----+---------+-------+------+-------+------------+
| pid | pname | pcode | aid | pcode | saletime |
+-----+---------+-------+------+-------+------------+
| 1 | 商品1 | AC90 | 3 | AC90 | 2008-09-23 |
+-----+---------+-------+------+-------+------------+
1 row in set (0.00 sec)
(4)加入count统计:
mysql> select *,count(s.aid) as total from products as p left join sales_detail as s on p.pcode=s.pcode where s.saletime in ('2008-09-23','2008-09-24') group by p.pcode;
+-----+---------+-------+------+-------+------------+-------+
| pid | pname | pcode | aid | pcode | saletime | total |
+-----+---------+-------+------+-------+------------+-------+
| 1 | 商品1 | AC90 | 3 | AC90 | 2008-09-23 | 2 |
+-----+---------+-------+------+-------+------------+-------+
1 row in set (0.00 sec)
(5)改变查询出现了Null值变为1,如下,(接着就是如何去掉Null):
mysql> 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 |
+---------+-------+------------+-------+
3 rows in set (0.00 sec)
(6)用函数coalesce来去掉Null的情况:
mysql> select p.pname,p.pcode,coalesce(s.saletime,'0'),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 | coalesce(s.saletime,'0') | total |
+---------+-------+--------------------------+-------+
| 商品1 | AC90 | 2008-09-23 | 2 |
| 商品2 | DE78 | 0 | 0 |
| 商品3 | XXXX | 0 | 0 |
+---------+-------+--------------------------+-------+
(7)加入Mysql的时间函数带入查询,adddate函数日期带入where中的in查询情况:
mysql> select p.pname,p.pcode,coalesce(s.saletime,'0'),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',adddate(timestamp("2008-09-23"), interval 1 day))) group by p.pcode order by total desc,p.pid asc;
+---------+-------+--------------------------+-------+
| pname | pcode | coalesce(s.saletime,'0') | total |
+---------+-------+--------------------------+-------+
| 商品1 | AC90 | 2008-09-23 | 2 |
| 商品2 | DE78 | 0 | 0 |
| 商品3 | XXXX | 0 | 0 |
+---------+-------+--------------------------+-------+
3 rows in set (0.00 sec)
作者:jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美,在于尽态极妍 | 应用之美,在于药到病除
地址:http://jackxiang.com/post/4936/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明!
最后编辑: jackxiang 编辑于2012-1-31 18:11
评论列表
2012-2-16 01:30 | hxngb5lf
要看要看。謝謝分享哦
分页: 1/1 1