Table1
UserID Subject Score
1 En 100
1 Ma 30
1 Ch 70
2 Ma 90
2 En 30
3 Ma 90------------------------
最终效果:
UserID Subject Score
1 En 100
2 Ma 90
3 Ma 90--------------------
所用语句:
select * from Table1 A
where Not Exists
(select 1 from Table1 where UserID=A.UserID and Subject<A.Subject)另外还有一点:我的Table1是一个子查询(Select .......)
如果做效率更高一点:)
如何用子查询来替代Table1,保证执行速度
UserID Subject Score
1 En 100
1 Ma 30
1 Ch 70
2 Ma 90
2 En 30
3 Ma 90------------------------
最终效果:
UserID Subject Score
1 En 100
2 Ma 90
3 Ma 90--------------------
所用语句:
select * from Table1 A
where Not Exists
(select 1 from Table1 where UserID=A.UserID and Subject<A.Subject)另外还有一点:我的Table1是一个子查询(Select .......)
如果做效率更高一点:)
如何用子查询来替代Table1,保证执行速度
insert table1 select 1,'En',100
union all select 1,'Ma',30
union all select 1,'Ch',70
union all select 2,'Ma',90
union all select 2,'En',30
union all select 3,'Ma',90select a.* from table1 a,(select userid,max(score) as score from table1 group by userid) b where a.userid=b.userid and a.score=b.score
order by a.userid
select * from table1 a where(select count(1) from table1 where userid=a.userid and score>=a.score)<=1
2.select * from View1 A
where Not Exists
(select 1 from View1 where UserID=A.UserID and Subject<A.Subject)
where not exists (
select 1 from table1 where userid=a.userid and score<a.score )
insert @t select 1,'En',100
union all select 1,'Ma',30
union all select 1,'Ch',70
union all select 2,'Ma',90
union all select 2,'En',30
union all select 3,'Ma',90select * from @t a
where not exists (
select 1 from @t where userid=a.userid and score>a.score )
UserID Subject Score
----------- -------------------- -----------
1 En 100
2 Ma 90
3 Ma 90(所影响的行数为 3 行)
insert @t 子查询select * from @t A
where Not Exists
(select 1 from @t where UserID=A.UserID and Subject<A.Subject)
select * from Table1 A
where Not Exists
(select 1 from Table1 where UserID=A.UserID and Subject<A.Subject)
select * from Table1 A
where Not Exists
(select 1 from Table1 where UserID=A.UserID and Subject<A.Subject)