举例如下: create table a(id int,name varchar(10)) insert a values(1,'a') insert a values(2,'b') insert a values(1,'c') insert a values(2,'d') go declare @tab table(num int identity(1,1),id int,name varchar(10)) insert @tab select * from a select n.id,n.name from ( select id,min(num) min_num from @tab group by id) m inner join @tab n on m.min_num=n.num go 返回:id name ----------- ---------- 1 a 2 b(所影响的行数为 2 行)
create table t(id int,name varchar(10)) insert t values(1,'a') insert t values(2,'b') insert t values(1,'c') insert t values(2,'d') insert t values(1,'m') insert t values(2,'n') select * from t a where not exists(select 1 from t where id=a.id and name <a.name) id name ----------- ---------- 1 a 2 b(2 row(s) affected)
谢了,我用的是select * from t a where not exists(select 1 from t where id=a.id and name <a.name) 马上散分
create table a(id int,name varchar(10))
insert a values(1,'a')
insert a values(2,'b')
insert a values(1,'c')
insert a values(2,'d')
go
declare @tab table(num int identity(1,1),id int,name varchar(10))
insert @tab select * from a
select n.id,n.name
from (
select id,min(num) min_num
from @tab
group by id) m inner join @tab n on m.min_num=n.num
go
返回:id name
----------- ----------
1 a
2 b(所影响的行数为 2 行)
insert t values(1,'a')
insert t values(2,'b')
insert t values(1,'c')
insert t values(2,'d')
insert t values(1,'m')
insert t values(2,'n')
select * from t a
where not exists(select 1 from t where id=a.id and name <a.name)
id name
----------- ----------
1 a
2 b(2 row(s) affected)
where not exists(select 1 from t where id=a.id and name <a.name) 马上散分