时间段统计。。返回零值

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)

select c,cdate,count(a.Fd01)

from calendar c left jion tableA a on c.cdate=a.cdate

where c.cdate between '2008-01-01- and '2008-01-04'

group by c.cdate;