union all与空字段的一种用法

最新推荐文章于 2024-04-24 14:00:00 发布

weixin_34178244

最新推荐文章于 2024-04-24 14:00:00 发布

阅读量2.8k

点赞数

文章标签：数据库 python 大数据

原文链接：https://my.oschina.net/u/585111/blog/219479

版权

2019独角兽企业重金招聘Python工程师标准>>>

一个题目，觉得这是个非常经典的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`;