一个表TB
ID,UserName,Score,T_Time,TB2_ID现在要查出某个TB2_ID,UserName的最大Score
即:select UserName,TB2_ID,max(Score) as max_Score from TB where TB2_ID=23 group by UserName,TB2_ID但是WEB中要提取的信息ID,UserName,Score,T_Time,TB2_ID
这个语句该怎么写呢?
ID,UserName,Score,T_Time,TB2_ID现在要查出某个TB2_ID,UserName的最大Score
即:select UserName,TB2_ID,max(Score) as max_Score from TB where TB2_ID=23 group by UserName,TB2_ID但是WEB中要提取的信息ID,UserName,Score,T_Time,TB2_ID
这个语句该怎么写呢?
from tb t
where not exists(select * from tb where t.UserName<UserName and TB2_ID=t.TB2_ID)
from tb t
where not exists(select 1 from tb where TB2_ID=t.TB2_ID and t.UserName<UserName )
select ID,UserName,max_Score,T_Time,TB2_ID
from TB,(select UserName,TB2_ID,max(Score) as max_Score from TB where TB2_ID=23 group by UserName,TB2_ID)temp
where TB.UserName=temp.UserName and TB.TB2_ID=temp.TB2_ID and TB.Score=temp.max_Score
from (select UserName,TB2_ID,Score from TB where TB2_ID=23) tc
where score >=all (select score from tb where TB2_ID=23)
;
select * from tb t
where not exists(select 1 from tb where TB2_ID=t.TB2_ID and t.UserName<UserName )
from tb t
where not exists(select * from tb where t.UserName<UserName and TB2_ID=t.TB2_ID)
这是个什么写法?可以解释一下吗?
这句话就是过渡出最大的username
意思就是 不存在ID相同的 但是子查询出来的USERNAME比传进来的要大 那就说明传进来的username是最大的。
select ID,UserName,Score,T_Time,TB2_ID
from tb t
where not exists(select * from tb where t.UserName=UserName and TB2_ID=t.TB2_ID and t.score <score)
那就这样