將它連接到僅有最大計數的第二個查詢。每天最內部查詢(對于給定用戶)每天計數的一組行數。從那以后，下一個外部執行從該集合中選擇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