create table #T(name char(10),id char(1),countx int)
insert into #T
select 'A','1',20
union
select 'A','2',30
union
select 'B','1',30
union
select 'B','2',20
union
select 'C','1',40select #t.name,#t.id,#t.countx from #T inner join
(select name,min(id) id from #t group by name) b
on #T.name=b.name and #T.id=b.iddrop table #T上面需要进行inner join 连接才能完成
有没有更好的方法,更简洁的方法
insert into #T
select 'A','1',20
union
select 'A','2',30
union
select 'B','1',30
union
select 'B','2',20
union
select 'C','1',40select #t.name,#t.id,#t.countx from #T inner join
(select name,min(id) id from #t group by name) b
on #T.name=b.name and #T.id=b.iddrop table #T上面需要进行inner join 连接才能完成
有没有更好的方法,更简洁的方法
create table #T(name char(10),id char(1),countx int)
insert into #T
select 'A','1',20
union
select 'A','2',30
union
select 'B','1',30
union
select 'B','2',20
union
select 'C','1',40select * from #T a
where a.id=(select MIN(id) from #T b where a.name=b.name)
/*
name id countx
----------------------------
A 1 20
B 1 30
C 1 40
*/
where a.id>b.id)
FROM #T AS t1
WHERE NOT EXISTS(SELECT 1 FROM #T AS t2 WHERE t1.name = t2.name AND t1.id > t2.id)name id countx
---------- ---- -----------
A 1 20
B 1 30
C 1 40
where a.id=(select MIN(id) from #T b where a.name=b.name)
where a.id not in(select MAX(id) from #T b where a.name=b.name having count(name)>1)
FROM #T AS t1
WHERE EXISTS(SELECT 1 FROM #T AS t2 WHERE t1.name = t2.name AND t1.id < t2.id)
我比较喜欢用exists而不是not exists,试试这个速度会不会快点。