求sql语句，在线等！

[fairykengqiangmeigui]

表 a：
id name age
1 a 12
2 b 11
3 c 10
4 a 12
5 b 11

结果集：
id name age
1 a 12
2 b 11
3 c 10
即：想用sql语句实现除去name和age都相同的记录。并且一定要显示id

[RYAN--333]

select * from tb t
where not exists(select * from tb where name=t.name and age=t.age and id<t.id)

[--小F--]

select
 * 
from
 tb t
where
 id=(select min(id) from tb where name=t.name and age=t.age )

[smntbk]

select min(id) as id,[name],[age] from t group by name,age

[sinpoal]

if object_id('ta')is not null drop table ta
create table ta(id int,[name] char(1),age int)
insert ta
select 1,'a',12 union all
select 2,'b',11 union all
select 3,'c',10 union all
select 4,'a',12 union all
select 5,'b',11


select * from ta t where not exists(select * from ta where [name]=t.[name] and age=t.age and id<t.id)

[watermelon_2008]

select max(id) as id, name, age from tb group name,age

[sych888]

select min(id) id,name,age from ta group by name,age order by id

[pengxuan]

if object_id('tb') is not null
   drop table tb
go
create table tb(id int identity(1,1),name varchar(10),age int)
go
insert into tb
select 'a',12 union all
select 'b',11 union all
select 'c',10 union all
select 'a',12 union all
select 'b',11
go
select id,name,age from tb t1 where not exists(select 1 from tb where name=t1.name and age=t1.age and id<t1.id)

[--小F--]

[Quote=引用 8 楼 fairykengqiangmeigui 的回复:]
引用 1 楼 js_szy 的回复:
SQL codeselect*from tb twherenotexists(select*from tbwhere name=t.nameand age=t.ageand id <t.id)

这种方法试过了，可以是可以，可是当数据量很大，几万行时，执行的很慢很慢，有没有什么提高效率的法子呢？
[/Quote]
按照5楼 name ,age字段加索引

[wxq4100798]

select id,name,age from ta group by name,age order by id

[nianran520]

select id=min(id),name,age from 表名 group by name,age

[fairykengqiangmeigui]

[Quote=引用 1 楼 js_szy 的回复:]
SQL codeselect*from tb twherenotexists(select*from tbwhere name=t.nameand age=t.ageand id<t.id)
[/Quote]
这种方法试过了，可以是可以，可是当数据量很大，几万行时，执行的很慢很慢，有没有什么提高效率的法子呢？

[忆轩辕]

select min(id) as id,name,age from 表名 group by name,age

[破折号]

有个自身连接，速度肯定慢了。。。最多把子查询*改成1 不过效果不会太明显

select * from tb t
where not exists(select 1 from tb where name=t.name and age=t.age and id<t.id)

[雨青]

select id=min(id),name,age from 表名 group by name,age

[狼王_]

select id=(min)id,name,age from tb t
where not exists(select * from tb where [name]=tb.[name] and age=tb.age)

[狼王_]

select id=(min)id,name,age from tb t
where not exists(select * from t where [name]=t.[name] and age=t.age)

[zhangwike]

顶

[fairykengqiangmeigui]

感谢大家，我要结贴啦

[gz284596265]

[Quote=引用 18 楼 wxq4100798 的回复:]
select id,name,age from ta group by name,age order by id
[/Quote]
id必须使用聚合函数 否则无法执行

[gz284596265]

select min(id) as id,name,age from a group by name,age order by id