求数据库脚本大神，增加列查询
早2天发过一个帖子求助，没很好的解决这个问题，我本身是在hive中执行脚本（hive只支持from后面的子查询），当然可以直接执行的最好了。先谢谢各位：
tb_records
原数据如图：
只能在from后面使用子查询，动态添加2列，求相同num，其他时间在当前行时间过去3天内的记录数和过去7天内的记录数。如图：
-- ----------------------------
-- Table structure for `tb_records`
-- ----------------------------
DROP TABLE IF EXISTS `tb_records`;
CREATE TABLE `tb_records` (
`name` varchar(20) DEFAULT NULL,
`num` varchar(20) DEFAULT NULL,
`date` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_records
-- ----------------------------
INSERT INTO tb_records VALUES ('张三', '0002', '2013-12-12 00:23:24');
INSERT INTO tb_records VALUES ('李四', '0002', '2013-12-15 00:23:24');
INSERT INTO tb_records VALUES ('王五', '0002', '2013-12-20 00:23:24');
INSERT INTO tb_records VALUES ('赵六', '0002', '2013-12-30 00:23:24');
INSERT INTO tb_records VALUES ('王二', '0005', '2013-12-13 00:23:24');
INSERT INTO tb_records VALUES ('麻子', '0005', '2013-12-13 00:23:24');
也就是要将这个sql的子查询放到from后面处理。
select a.*,(select count(1) from tb_records b
where b.num = a.num and b.date <= a.date
and datediff(str_to_date(a.date,'%Y-%m-%d %H:%i:%s.%f'),str_to_date(b.date,'%Y-%m-%d %H:%i:%s.%f')) between 0 and 3 ) as count3,
(select count(1) from tb_records b
where b.num = a.num and b.date <= a.date
and datediff(str_to_date(a.date,'%Y-%m-%d %H:%i:%s.%f'),str_to_date(b.date,'%Y-%m-%d %H:%i:%s.%f')) between 0 and 7 ) as count7
from tb_records a ;
[解决办法]
select a.name,a.num,a.date,count3,
count(*) as count7
from
(
select a.name,a.num,a.date,
COUNT(*) as count3
from tb_records a
left join tb_records b
on b.num = a.num and b.date <= a.date
and datediff(str_to_date(a.date,'%Y-%m-%d %H:%i:%s.%f'),str_to_date(b.date,'%Y-%m-%d %H:%i:%s.%f')) between 0 and 3
group by a.name,a.num,a.date
)a
left join tb_records b
on b.num = a.num and b.date <= a.date
and datediff(str_to_date(a.date,'%Y-%m-%d %H:%i:%s.%f'),str_to_date(b.date,'%Y-%m-%d %H:%i:%s.%f')) between 0 and 7
group by a.name,a.num,a.date,count3
