select distinct name from a select max(ID) id , NAME from A group by name select min(ID) id , NAME from A group by name
--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (ID int,NAME varchar(11)) insert into #T select 1,'aa' union all select 2,'bb' union all select 3,'bb'-->留最大ID select * from #T as t where not exists (select 1 from #T where Name=t.Name and id>t.id) /* ID NAME ----------- ----------- 1 aa 3 bb */-->留最小ID select * from #T as t where not exists (select 1 from #T where Name=t.Name and id<t.id) /* ID NAME ----------- ----------- 1 aa 2 bb */
select min(id),name from a group by name
如有表A ID NAME 1 aa 2 bb 3 bb 怎么查询到所有值,但是NAME不能有重复值, 如果select NAME from A group by NAME,只是把NAME的无重复值查询出来。 distinct、group by 可以过滤重复, 最直观的 select distinct * from emp select name,age from emp group by name,age
group by和distinct能显示不重复,但是只能显示某一字段的值。我要select *,但是某一字段的值不能有重复
不太明白你的意思。select,如果有记录如下:name f1 aa 111 aa 222楼主是要选出一条,还是两条?
select max(ID) id , NAME from A group by name
select min(ID) id , NAME from A group by name
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,NAME varchar(11))
insert into #T
select 1,'aa' union all
select 2,'bb' union all
select 3,'bb'-->留最大ID
select * from #T as t where not exists (select 1 from #T where Name=t.Name and id>t.id)
/*
ID NAME
----------- -----------
1 aa
3 bb
*/-->留最小ID
select * from #T as t where not exists (select 1 from #T where Name=t.Name and id<t.id)
/*
ID NAME
----------- -----------
1 aa
2 bb
*/
ID NAME
1 aa
2 bb
3 bb 怎么查询到所有值,但是NAME不能有重复值,
如果select NAME from A group by NAME,只是把NAME的无重复值查询出来。
distinct、group by 可以过滤重复,
最直观的
select distinct * from emp
select name,age from emp group by name,age
aa 111
aa 222楼主是要选出一条,还是两条?