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

[Rotel-刘志东]

select distinct(a),dictinct(b),c,d,e 
from table 
group by c,d,e

[SQL77]

SELECT * FROM TB T WHERE B=(SELECT MAX(B) FROM TB WHERE A=T.A)

[Rotel-刘志东]

--不好意思上边写错了
select distinct(a),distinct(b),c,d,e 
from table
group by c,d,e

[快溜]

select * from tb a 
 where not exists(select 1 from 
    (select a,b from tb group by a,b having count(1)>1) b where b.a=a.a and b.b=a.b)

[yqlx521]

[Quote=引用 3 楼 lzd_83 的回复:]
SQL code
--不好意思上边写错了
select distinct(a),distinct(b),c,d,e
from table
group by c,d,e
[/Quote]

这样好像执行不了吧

[快溜]

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 

select * from tb a 
 where not exists(select 1 from 
    (select a,b from tb group by a,b having count(1)>1) b where b.a=a.a and b.b=a.b)

/*

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

[yqlx521]

[Quote=引用 6 楼 ssp2009 的回复:]
SQL code

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 unio……
[/Quote]

谢谢你 多表的话 这么写应该也可以吧

[叶子]

declare @table table (a int,b int,c int,d int,e int)
insert into @table
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 bb.* from (select a,b from @table group by a,b having (count(1)=1))
aa left join @table bb on aa.a=bb.a and aa.b=bb.b

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

[cnkiminzhuhu]

[Quote=引用 2 楼 sql77 的回复:]

SELECT * FROM TB T WHERE B=(SELECT MAX(B) FROM TB WHERE A=T.A)
[/Quote]
你这个查询是不对的 在加两条记录可知

create table #
(a int,b int,c int,d int, e int)
insert into # 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 union all
  select 2,5,5,5,5 union all
  select 2,5,5,5,5

[cnkiminzhuhu]

[Quote=引用 3 楼 lzd_83 的回复:]

SQL code
--不好意思上边写错了
select distinct(a),distinct(b),c,d,e
from table
group by c,d,e
[/Quote]
有语法错误

[cnkiminzhuhu]

6楼跟8楼的方法是正确的，8楼的查询方法是不是效率更高些？