我有这个查询：
select
id_re_usr,
year(time) as AYear,
DAYOFYEAR(time) as ADay,
DATE_FORMAT(time, "%m-%d-%y") as date,
count(*) as TotalPerDay
from numrequest
where id_re_usr = "u1"
group by id_re_usr, AYear, ADay
order by AYear, ADay
它输出类似的东西
date TotalPerDay
------------------------
01-01-87 1
01-09-12 5
02-09-12 17
03-09-12 1
如何在不使用php或更改查询来更改当前输出的情况下找到最大TotalPerDay。
我试过这样做，它有效
$max=0;
while($row=mysql_fetch_array($results)){
if($max<$row['TotalPerDay']){ $max= $row['TotalPerDay'];}
}
但是没有直接的方法吗？
如果修改查询，输出应该是
date TotalPerDay max
----------------------------------------
01-01-87 1 17
01-09-12 5 17
02-09-12 17 17
03-09-12 1 17
将其连接到第二个仅为最大计数的查询中。。每天最内部的查询（对于给定用户）是一组按计数每天分组的行。从中，下一个外部执行从该集合中选择MAX（），以查找并仅获得一条表示最高天数的记录。。。由于它总是返回一行，并连接到原始的numRequest表，因此它将是笛卡尔的，但没有问题，因为它只有一条记录，而且无论如何，您都希望每个返回的行都有这个值。
select
id_re_usr,
year(time) as AYear,
DAYOFYEAR(time) as ADay,
DATE_FORMAT(time, "%m-%d-%y") as date,
count(*) as TotalPerDay,
HighestCount.Max1 as HighestOneDayCount
from
numrequest,
( select max( CountsByDate.DayCount ) Max1
from ( select count(*) as DayCount
from numrequests nr
where nr.id_re_usr = "u1"
group by date( nr.time )) CountsByDate
) HighestCount
where
id_re_usr = "u1"
group by
id_re_usr,
AYear,
ADay
order by
AYear,
ADay