--1
declare @dt datetime
set @dt='2008-07-18 'select * from tbname t
where playTime between @dt and dateadd(dd,30,@dt)
and not exists(
select 1 from tbname where name=t.name and score>t.score
)--2
select name,convert(varchar(10),playTime,120),max(score) as score
from tbname
group by name ,convert(varchar(10),playTime,120)
declare @dt datetime
set @dt='2008-07-18 'select * from tbname t
where playTime between @dt and dateadd(dd,30,@dt)
and not exists(
select 1 from tbname where name=t.name and score>t.score
)--2
select name,convert(varchar(10),playTime,120),max(score) as score
from tbname
group by name ,convert(varchar(10),playTime,120)
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html
from (select name,max(score) as score from test) a
left join test b on a.name=b.name and a.score=b.scroe
order by score desc,playtime
select top 1 name,score,playtime
from test
where convert(char(10),uploadtime,21)=convert(char(10),getdate(),21)
order by score desc,playtime
表名叫scores,
select top 30 m.* from tb m where convert(varchar(10),m.uploadtime,120) = @dt and score = (select max(score) from tb n where convert(varchar(10),n.uploadtime,120) = @dt and n.name = m.name) order by m.name (--or order by m.uploadtime)--2
select m.* from tb m where score = (select max(score) from tb n where convert(varchar(10),n.uploadtime,120) = convert(varchar(10),m.uploadtime,120) ) order by m.uploadtime
打印出来的有 name,score,playTime三个值。
1、指定天是指1天,还是一个时间段内。
2、如果用户最高分值有多条(如200分有很多条),是否多条都显示,还是只显示1条。第二个语句是:
求每一天的最高用户分数,显示出来的有name,score,playTime
1、是每个用户那一天的最高分数的记录(多条记录,每人一条),还是那一天分最高的用户的记录(只有一条)。
--每个用户每一天的最高分数的记录
select name,convert(varchar(10),playtime,120) playdate,max(score) maxscore
from scores
group by name,convert(varchar(10),playtime,120)
order by name,convert(varchar(10),playtime,120)那一天分最高的用户的记录
select a.*
from scores a
inner join (select convert(varchar(10),playtime,120) playdate,max(score) maxscore
from scores
group by convert(varchar(10),playtime,120)) b
on convert(varchar(10),a.playtime,120) = b.playdate
and a.score = b.maxscore
假如数据库中有
2007.12.5
2007.12.6
2007.12.7
这几个日期,我要取出每一天的最高分数记录,就是一天只有一个记录。如果分数一样,就取他所花的时间
uploadTime:玩游戏时所在的日期
playTime: 所花的时间就是这种意思,谢谢。
第二个问题,我已经解决了,但是我觉得这种方式写得太麻烦了,不知道有没有简便的方法
这是语句: select * from scores
where score in(select max(score) from scores group by convert(char(10),uploadTime,120)
)
and playtime in(select min(playtime) from scores group by convert(char(10),uploadTime,120))order by uploadtime desc,score desc,playtime