时间段统计。返回零值解决方法

时间段统计。。返回零值

表

2008-01-01 A

2008-01-01 B

2008-01-02 C

2008-01-04 D

时间段

2008-01-01-2008-01-0４

返回

2008-01-01 2

2008-01-02 1

2008-01-03 0

2008-01-04 1

这个问题讨论了很多次.

解决方案

建一个日历表

2008-01-01

2008-01-02

2008-01-03

2008-01-04

…

然后用left join

== 思想重于技巧 ==

SQL code

mysql> use t_girl
Database changed
mysql> create table c_t (d_field date not null, n_field char(1) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table tmp (d_field date not null);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tmp values('2008-01-01'),('200-01-02'),('2008-01-03'),('2008-01-04');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> load data infile '/tmp/t.sql' into table c_t;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from c_t;
+------------+---------+
| d_field    | n_field |
+------------+---------+
| 2008-01-01 | A       | 
| 2008-01-01 | B       | 
| 2008-01-02 | C       | 
| 2008-01-04 | D       | 
+------------+---------+
4 rows in set (0.00 sec)

mysql> select a.d_field,sum((case when b.d_field is null then 0 else 1 end ))as num from tmp as a left join c_t as b using(d_field) group by a.d_field order by a.d_field asc;
+------------+------+
| d_field    | num  |
+------------+------+
| 2008-01-01 |    2 | 
| 2008-01-02 |    1 | 
| 2008-01-03 |    0 | 
| 2008-01-04 |    1 | 
+------------+------+
4 rows in set (0.00 sec)