表如下: A B C D
-------------------------
1 0 2 2
1 1 0 2
2 0 5 6
2 0 6 5
3 1 2 9根据A字段去重复,且取出B字段最大的那条记录,如果B相同,则取D最大的那条记录
查询结果如下: A B C D
-------------------------
1 1 0 2
2 0 5 6
3 1 2 9
-------------------------
1 0 2 2
1 1 0 2
2 0 5 6
2 0 6 5
3 1 2 9根据A字段去重复,且取出B字段最大的那条记录,如果B相同,则取D最大的那条记录
查询结果如下: A B C D
-------------------------
1 1 0 2
2 0 5 6
3 1 2 9
declare @Tbl table(A int, B int, C int, D int)insert into @tbl
select 1 A,0 B,2 C,2 D
union all select 1,1,0,2
union all select 2,0,5,6
union all select 2,0,6,5
union all select 3,1,2,9--方式一
select A, Max(B) B, Min(C), Max(D) from @Tbl group by a --方式二
select B.* from(
select A, Max(B) B, Max(D) D from @Tbl group by A) A left join @Tbl B on A.A=B.A and A.B=B.B and A.D=B.D
from tb t
where not exists(select 1 from tb where b>t.b or (b=t.b and d>t.d))
select *
from tb t
where not exists(select 1 from tb where a=t.a and (b>t.b or (b=t.b and d>t.d)))
select *
from tb t
where not exists(select 1 from tb where a=t.a and (b>t.b or (b=t.b and d>t.d)))