Question

sql问题 查询总积分排前五十的用户后进行排列，如果总积分相同，则按照达到该积分的先后顺序排列

现在的语句是sql ="select top 50 sum(jifen) as jifen_sum ,(select username from userlist where id = userid ) as username from JifenList where record like '获得%积分' and day(time)=day(getdate()) and month(time)=month(getdate()) and year(time)=year(getdate()) group by userid order by jifen_sum desc"
表JifenList的结构如下：【time是通过now()获取的】
ID UserID Jifen Record Time
1 a 100 获得100积分 2015-8-11 10:10:00
2 b 200 获得200积分 2015-8-11 10:20:00
3 c 300 获得300积分 2015-8-11 10:30:00
4 c 100 获得100积分 2015-8-11 10:40:00
5 a 300 获得300积分 2015-8-11 10:50:00
执行上面的语句的结果就是
排行 User jifen_sum reward
1 a 400 100
2 c 400 50
3 b 200 30
但我想要的结果是：因为c比a先达到400的总积分，所以应该把c排在第一位
只有这么多分了，请见谅！

Answer 1

select top 50 sum(jifen) as jifen_sum ,(select username from userlist where id = userid ) as username from JifenList where record like '获得%积分' and day(time)=day(getdate()) and month(time)=month(getdate()) and year(time)=year(getdate()) group by userid order by jifen_sum desc，time 即可

追问

加了个time以后查询不到任何数据，加[time]也一样

Answer 2

你可以在合计积分的后面加上 max(time) as curr_time，之后 orderby 中加入 curr_time

Answer 3

select jf.UserID,sum(jf.Jifen) as s,max(jf.Time) as t from JifenList
as jf group by jf.UserID order by s desc,t

追问

查询结果为空