ID SM NUM FEN RIQI
4 1 8613077737645 10 200605
8 0 13978649450 20 200604
9 0 13978649450 30 200605
10 0 13978649450 40 200606
5 0 13978649450 2 200601
6 0 13978649450 6 200602
7 0 13978649450 8 200603
1 2 07712969830 4 200604
2 2 07712969830 6 200605如今我想得到的结果是:ID SM NUM FEN RIQI
10 0 13978649450 40 200606
4 1 8613077737645 10 200605
2 2 07712969830 6 200605得到最高分的成绩.并且不能返回相同号码的记录.
4 1 8613077737645 10 200605
8 0 13978649450 20 200604
9 0 13978649450 30 200605
10 0 13978649450 40 200606
5 0 13978649450 2 200601
6 0 13978649450 6 200602
7 0 13978649450 8 200603
1 2 07712969830 4 200604
2 2 07712969830 6 200605如今我想得到的结果是:ID SM NUM FEN RIQI
10 0 13978649450 40 200606
4 1 8613077737645 10 200605
2 2 07712969830 6 200605得到最高分的成绩.并且不能返回相同号码的记录.
Inner Join (Select SM,Max(FEN) As FEN From TableName Group By SM) B
On A.SM=B.SM And A.FEN=B.FEN
Select A.* From TableName A
Where Not Exists (Select 1 From TableName Where SM=A.SM And FEN>A.FEN)
Select A.* From TableName A
Where FEN= (Select Max(FEN) From TableName Where SM=A.SM)
from tablename a
inner join
(
select num,max(fen) fen
from tablename
group by num
)b
on
a.num=b.num
and
a.fen=b.fen
select * from tb where exists(select max(fen) from tb group by sm) order by fen desc
-----------------------------
語句錯誤
where id in
( select top 1 id from tb where num=A.num order by fen desc)
用
select * from tb where fen in(select max(fen) from tb group by sm) order by fen desc
结果为什么是这个,这里面有神么差别
10 0 13978649450 40.0 200606.0
4 1 8613077737645 10.0 200605.0
6 0 13978649450 6.0 200602.0
2 2 07712969830 6.0 200605.0