MySQL按天分组查询每天最大值出现的时间

[xiaxusong]

表：A(time,siteid,pac)
其中time是datetime类型，pac是float类型

我要查询每天所有siteid的在当天所有时间点的avg(pac)出现最大值的时间，请问该怎么写SQL？

我原先的SQL是这样的：
select t1.time,max(apac) as mpac from (select time,avg(Pac) as apac from A where time>="2017-09-20" and time<="2017-10-30" group by time) t1 group by DATE_FORMAT(time,"%Y-%m-%d");

可是这样查询出来的时间不对，是分组后每天的开始时间

我应该怎么改

[阳泉酒家小当家]

引用 2 楼 xiaxusong 的回复:

[quote=引用 1 楼 rucypli 的回复:] select * from A a1 where not exits (select 1 from A a2 where date(a1.time)=date(a2.time) and a1.siteid<a2,siteid ) order by a1.time desc;

a1,a2是什么东西啊，买看明白[/quote] a1，a2是表的别称，因为要连续用两次A表，所以要起个别称a1，a2，来区分

[rucypli]

select * from A a1 where not exits (select 1 from A a2 where date(a1.time)=date(a2.time) and a1.siteid<a2,siteid ) order by a1.time desc;

[xiaxusong]

引用 1 楼 rucypli 的回复:

select * from A a1 where not exits (select 1 from A a2 where date(a1.time)=date(a2.time) and a1.siteid<a2,siteid ) order by a1.time desc;

a1,a2是什么东西啊，买看明白