为啥要放到后面呢?程序需要么??select p.* 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
)p
hive不支持from前面的子查询,只支持from后面有子查询
hive不支持from前面的子查询,只支持from后面有子查询你用的是Mysql把, ENGINE=InnoDB DEFAULT CHARSET=utf8; 这个是InnoDB数据库引擎哈。
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