一套答题系统,每个人可以做多次,统计的时候只统计每个人的最高分数.tba表id email score
1 [email protected] 40
2 [email protected] 30
3 [email protected] 30
4 [email protected] 60
5 [email protected] 50
6 [email protected] 60
结果:id email score
4 [email protected] 60
5 [email protected] 50
6 [email protected] 60
1 [email protected] 40
2 [email protected] 30
3 [email protected] 30
4 [email protected] 60
5 [email protected] 50
6 [email protected] 60
结果:id email score
4 [email protected] 60
5 [email protected] 50
6 [email protected] 60
解决方案 »
- 在SQL 2005体验版中导出数据失败~~在线等(找wufeng45552)
- 如何select一个表中分组最值,请进
- 出现错误"未能在数据库'MSDB'中运行BEGIN TRANSACTION,因为该数据库处于回避恢复模式"
- 请教一个sql语句的优化,我在子查询中用到count(distinct)的时候,效率低的不行
- 简单的SQL,100分先答的给全分!!!!!!!!
- 急!急!大家帮忙想个VB与SQL连接的课设题目!在线等!
- 关于附加数据库问题.
- 高手帮个忙!!谢谢!!
- 如何将一个表的列值变为另一个表的行值
- 请教:winfrom程序中远程访问sqlser2000服务器的问题
- 高手求助SELECT
- 关于时间的查询
(
select 1 from t where email=a.email and id>a.id
)
group by email
(
select 1 from t where email=a.email and score>a.score
)
create table t(id int,email varchar(20),score int)
insert t select 1,'[email protected]',40
union all select 2,'[email protected]',30
union all select 3,'[email protected]',30
union all select 4,'[email protected]',60
union all select 5,'[email protected]',50
union all select 6,'[email protected]',60
select * from t a where not exists
(
select 1 from t where email=a.email and id>a.id
)drop table tid email score
----------- -------------------- -----------
4 [email protected] 60
5 [email protected] 50
6 [email protected] 60
select * from t a where not exists
(
select 1 from t where email=a.email and id>a.id
)
你把咖啡熊的id>a.id换成score>a.score
from tba t1,
(
select email,max(score) as score
from tba
group by email
) T2
where T1.email=t2.email
select a.id,b.email,b.score from t a
left join
(
select email,max(score)as score from t
group by email
)b a.score=b.score and a.email=b.email
insert into ttselect 1, '[email protected]', 40 union all
select 2, '[email protected]' , 30 union all
select 3, '[email protected]' , 30 union all
select 4, '[email protected]' , 60 union all
select 5, '[email protected]', 50 union all
select 6, '[email protected]' , 60select a.id,b.email,b.score from tt a
join
(
select email,max(score)as score from tt
group by email
)b on a.score=b.score and a.email=b.email
order by a.id
id email score
------ ---------- ------
4 [email protected] 60
5 [email protected] 50
6 [email protected] 60(所影响的行数为 3 行)