--测试 create table aa (code int,name varchar(10)) insert aa select 11,'a' union all select 11,'b' union all select 12,'c' union all select 13,'d' union all select 14,'fg' union all select 14,'ft' union all select 15,'kl' select identity(int,1,1),* into #a from aa select code,name from #a where id in (select max(id) from #a group by code) drop table aa drop tabel #a /* 结果 code name 11,a 12,c 13,d 14,fg 15,kl */
随便说一下 select distinct code,name from aa 是比较code,name两个字段都不能有相同的记录 如果是 11,a 11,a 12,b 13,c 的话 用select distinct code,name from aa 的结果就会为 11,a 12,b 13,c
注意,Distinct 后面每个字段都参与运算的!!
如果该表有主键的话,假定为ID Select code, name From aa A Where A.ID=( Select Top 1 ID From aa where A.code=code )希望你能理解这段代码
select * from aa a where name =(select min(name) from aa where code=a.code )
create table aa (code int,name varchar(10))
insert aa
select 11,'a'
union all select 11,'b'
union all select 12,'c'
union all select 13,'d'
union all select 14,'fg'
union all select 14,'ft'
union all select 15,'kl'
select identity(int,1,1),* into #a from aa
select code,name from #a where id in (select max(id) from #a group by code)
drop table aa
drop tabel #a
/*
结果
code name
11,a
12,c
13,d
14,fg
15,kl
*/
select distinct code,name from aa 是比较code,name两个字段都不能有相同的记录
如果是
11,a
11,a
12,b
13,c
的话
用select distinct code,name from aa
的结果就会为
11,a
12,b
13,c
Select code, name From aa A
Where A.ID=(
Select Top 1 ID From aa where A.code=code
)希望你能理解这段代码