34,838
社区成员




SELECT * FROM TableName a
WHERE 字段1=(
SELECT TOP 1 字段1
FROM TableName
WHERE 字段2=a.字段2
)
--测试数据
create table 表a(字段1 nvarchar(20),字段2 nvarchar(20),字段3 nvarchar(20))
insert into 表a
select 't1','d1','e1' union all
select 't2','d2','e1' union all
select 't3','d1','e3' union all
select 't4','d3','e4' union all
select 't5','d2','e5
/*------------------------
select * from 表a a where not exists (select 1 from 表a where a.字段2 = 字段2 and 字段1<a.字段1)
------------------------*/
字段1 字段2 字段3
-------------------- -------------------- --------------------
t1 d1 e1
t2 d2 e1
t4 d3 e4
(3 行受影响)
create table tb(字段1 varchar(10), 字段2 varchar(10), 字段3 varchar(10))
insert into tb values('t1' , 'd1' , 'e1')
insert into tb values('t2' , 'd2' , 'e1')
insert into tb values('t3' , 'd1' , 'e3')
insert into tb values('t4' , 'd3' , 'e4')
insert into tb values('t5' , 'd2' , 'e5')
go
select a.* from tb a where 字段1 = (select top 1 字段1 from tb where 字段2 = a.字段2) order by a.字段2
drop table tb
/*
字段1 字段2 字段3
---------- ---------- ----------
t1 d1 e1
t2 d2 e1
t4 d3 e4
(所影响的行数为 3 行)
*/
select a.*
from ta a
right join ( select 字段2,min(字段3) as 字段3
from ta
group by 字段2) b
on a.字段2 = b.字段2 and a.字段3=b.字段3
SELECT * FROM TableName a
WHERE 字段1=(
SELECT TOP 1 字段1
FROM TableName
WHERE 字段2=a.字段2
)