从A表中得出按其中某一字段(m)排序的记录集,要求得出记录是基中n字段不重复,凡n字段重复的只保留m最大的一条:
表名:A
m n l y
1 1000 43 4
2 1000 56 9
3 1002 88 4
4 1005 98 8
5 1006 78 99
6 1008 86 100
7 1006 87 98
得到结果是:
m n l y
2 1000 56 9
3 1002 88 4
4 1005 98 8
6 1008 86 100
7 1006 87 98
表名:A
m n l y
1 1000 43 4
2 1000 56 9
3 1002 88 4
4 1005 98 8
5 1006 78 99
6 1008 86 100
7 1006 87 98
得到结果是:
m n l y
2 1000 56 9
3 1002 88 4
4 1005 98 8
6 1008 86 100
7 1006 87 98
where a.n = b.n and a.l = b.l
where a.n = b.n and a.m = b.m
insert A(n,l,y) select 1000,43,4
insert A(n,l,y) select 1000,56,9
insert A(n,l,y) select 1002,88,4
insert A(n,l,y) select 1005,98,8
insert A(n,l,y) select 1006,78,99
insert A(n,l,y) select 1008,86,100
insert A(n,l,y) select 1006,87,98
--select * from ASELECT * FROM A i WHERE not exists
(
SELECT 1 FROM A WHERE m>i.m and n=i.n
)drop table A
where a.n = b.n and a.m = b.m
dawugui(潇洒老乌龟) 的可以解决问题.下面一个可能复杂一些:
select * from a , (select top 1 n,max(m) as m from a group by n order by l desc) b
where a.n = b.n and a.m = b.m
where m
in (select max(m)as m from a group by n)
(select b.*,t=case when m=(select m=max(a.m) from a where a.n = b.n group by n)then 1 else 0 end
from a b)c
where t=1