我有这样的疑问:
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发布于 2012-02-09 22:26:29
将其连接到第二个仅包含最大计数的查询中。每天最内部的查询(对于给定用户)是一组每天分组的count行。在此基础上,下一个外部从该集合中执行select 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,
ADayhttps://stackoverflow.com/questions/9210553
复制相似问题