如何获取date_of_highest_points

Question

我仍然对选择date_of_highest_points有问题：

$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

Answer 2

尝尝这个

select * from table_name where highest_points = (select max(highest_points) from table_name)

Answer 3

未经测试：

SELECT DISTINCT nick, SUM(frags), SUM(points), SUM(hours), 
       MAX(lastdate), MAX(highest_points),
            (
            SELECT  lastdate As date_of_highest_points
            FROM    hraci
            WHERE   points =(SELECT MAX(points) FROM hraci)
            )
(rest of query. i.e. FROM and ORDER By's)