[原创]union all与空字段的一种用法

一个题目，觉得这是个非常经典的SQL语句。所以拿出来大家分享。因为想不出怎么叫合适，所以就暂且叫做空字段吧。
问题：
t1                                t2 
id    firstTime                   usid         lastTime  
1     2007-5-1                    1            2007-5-2 00:5:01 
2     2007-5-1                    2            2007-5-2 05:06:12 
3     2007-5-2                    3            2007-5-6 12:01:15 
4     2007-5-3                    4            2007-6-2 15:11:12 
5     2007-5-3                    5            2007-5-8 00:00:05 
6     2007-5-4  
7     2007-6-1  
8     2007-6-2  
9     2007-6-2 

输出结果：
time          count(firstTime)        count(lastTime) 
2007-5-1        2                        0 
2007-5-2        1                        2 
2007-5-3        2                        0 
2007-5-4        1                        0 
2007-5-5        0                        0
2007-5-6        0                        0
2007-5-7        0                        0
2007-5-8        0                        0
...........
........... 
2007-6-2        2  
我给的答案：

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `firstTime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `t2` (
  `usid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lastTime` datetime NOT NULL,
  PRIMARY KEY (`usid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

insert into t1(`firstTime`) values
('2007-5-1'),('2007-5-1'),('2007-5-2'),('2007-5-3'),('2007-5-3'),('2007-5-4'),('2007-6-1'),
('2007-6-2'),('2007-6-2');

insert into t2(`lastTime`) values
('2007-5-2'),('2007-5-2'),('2007-5-6'),('2007-6-2'),('2007-5-8');

select * from (
select cast(firstTime as char(10)) `time`,count(1) f_t,0 l_t from t1 group By `time`
union all
select cast(lastTime as char(10)) `time`,0 f_t, count(1) l_t from t2 group by `time`)T group by `time`;


 

输出：

query result(8 records)

time	f_t	l_t
2007-05-01	2	0
2007-05-02	1	0
2007-05-03	2	0
2007-05-04	1	0
2007-05-06	0	1
2007-05-08	0	1
2007-06-01	1	0
2007-06-02	2	0