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

时间段统计。。返回零值
表
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_girlDatabase changedmysql> 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: 0mysql> 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: 0mysql> 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)