http://www.jackxiang.com/index.php zh-cn http://www.jackxiang.com/post/4936/ jack <xdy108@126.com> Tue, 31 Jan 2012 09:07:37 +0000 http://www.jackxiang.com/post/4936/ 主要是对这两篇文章的一介总结：
[实践常用]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(销售记录 表)。如下:
products:       pid pname pcode     1   商品1   AC90     2   商品2 DE78      3   商品3    XXXX
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）。
-- 表的结构 `products`：
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`：
INSERT INTO `products` (`pid`, `pname`, `pcode`) VALUES (1, '商品1', 'AC90'), (2, '商品2', 'DE78'), (3, '商品3', 'XXXX');
表的结构 `sales_detail`：
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`:
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');
查询：
区别两条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)

]]> http://www.jackxiang.com/post/4936/#blogcomment63437 hxngb5lf <kcja74@mail114.net> Wed, 15 Feb 2012 17:30:14 +0000 http://www.jackxiang.com/post/4936/#blogcomment63437