mysql - 如何获取最高点日期

Question

我在选择最高点日期时仍然遇到问题:

$qry1 = mysql_query("INSERT INTO bbdnes_hraci (nick, sumfrags, sumpoints, 
             sumhours, lastdate1,  highest_points, date_of_highest_points)

SELECT DISTINCT nick, SUM(frags), SUM(points), SUM(hours), 
   MAX(lastdate), MAX(points), ?????? 

FROM hraci
GROUP BY nick  "); 

我在这里问过这个问题https://stackoverflow.com/questions/18817900/select-in-select ，但尽管有理事会，我还没有解决它。有人可以帮我编写代码吗？

表:hraci

nick  frags   points hours         lastdate              

Gamer1  20      100   1         2013-09-17 22:16:08

Gamer1  30      150   3         2013-09-18 20:17:15

Gamer1  25      125    0.5      2013-09-18 23:16:06

Gamer2  50      250    4        2013-09-17 21:11:30

Gamer2  5        25    2        2013-09-17 23:13:59

需要获取:

表:bbdnes_hraci

nick  sumfrags   sumpoints  sumhours     lastdate1   highest_points   date_of_highest_points

Gamer1  75         375         4.5       2013-09-18 23:16:06   150   2013-09-18 20:17:15 ??

Gamer2  55         275          6        2013-09-17 23:13:59   250  2013-09-17 21:11:30  ??

Answer 1

你可以这样做:

select nick, SUM(frags) sumfrags, SUM(points) sumpoints, SUM([hours]) sumhours, max(lastdate) lastdate, max(points) highest_points
  into #t1
  from hraci
 group by Nick

select t1.*, x.lastdate date_of_highest_points
  from #t1 t1
         outer apply
       (select top 1 lastdate 
          from hraci t
         where t.Nick = t1.Nick and t.points = t1.highest_points
         order by t.lastdate desc) x