100分求助 distinct + group by 续 在线等待

[yqlx521]

在 sql server 2008 下 实现
select a,b,c,d,e from table
我现在想把 a，b着个字段 重复的去掉 要怎么做啊
想得到如下结果 a b c d e
1 2 3 4 5
2 2 3 4 6
1 2 5 7 9
3 4 6 7 8
1 3 5 9 8

查询后结果 2 2 3 4 6
3 4 6 7 8
1 3 5 9 8
1 2 3 4 5 和 1 2 5 7 9 拿出一条就行

[打一壶酱油]

select a,b,c,d,e 
from （
SELECT *,ROW_NUMBER() OVER(PARTITION BY A,B ORDER BY C,D,E) AS NID
FROM [table]
) T WHERE NID=1

[lds1ove]

[Xiao_Ai_Mei]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb (a int,b int,c int,d int,e int)
insert into #tb
select 1,2,3,4,5 union all
select 2,2,3,4,6 union all
select 1,2,5,7,9 union all
select 3,4,6,7,8 union all
select 1,3,5,9,8

select * from #tb t
where not exists(select * from #tb where a=t.a and b=t.b and e>t.e)


a           b           c           d           e
----------- ----------- ----------- ----------- -----------
2           2           3           4           6
1           2           5           7           9
3           4           6           7           8
1           3           5           9           8

(4 row(s) affected)

[guguda2008]

select a,b,c,d,e 
from （
SELECT *,ROW_NUMBER() OVER(PARTITION BY A,B ORDER BY C,D,E) AS NID
FROM [table]
) T WHERE NID=1

[快溜]

create table tb (a int,b int,c int,d int,e int)
insert into tb
  select 1 ,2 ,3, 4, 5 union all
select 2 ,2 ,3, 4, 5 union all
select 1 ,2 ,5, 6, 7 union all
select 3 ,4 ,3, 4, 5 union all
select 1 ,3 ,5, 9, 8 


with cte as
(
    select row_number() over(partition by rtrim(a)+rtrim(b) order by c) as num,* from tb
)
select a,b,c,d,e from cte where num=1
/*
a           b           c           d           e
----------- ----------- ----------- ----------- -----------
1           2           3           4           5
1           3           5           9           8
2           2           3           4           5
3           4           3           4           5

[AcHerat]

create table tb (a int,b int,c int,d int,e int)
insert into tb
select 1 ,2 ,3, 4, 5 union all
select 2 ,2 ,3, 4, 5 union all
select 1 ,2 ,5, 6, 7 union all
select 3 ,4 ,3, 4, 5 union all
select 1 ,3 ,5, 9, 8 
go

with cte as
(
	select rn = row_number() over (partition by a,b order by c),*
	from tb
)
select a,b,c,d,e from cte where rn = 1

--or

select *
from tb a
where not exists (select 1 from tb where a = a.a and b = a.b and c > a.c)
order by a,b

drop table tb



a           b           c           d           e
----------- ----------- ----------- ----------- -----------
1           2           3           4           5
1           3           5           9           8
2           2           3           4           5
3           4           3           4           5

(4 行受影响)

a           b           c           d           e
----------- ----------- ----------- ----------- -----------
1           2           5           6           7
1           3           5           9           8
2           2           3           4           5
3           4           3           4           5

(4 行受影响)