create table a
(
字段1 varchar(1),
字段2 varchar(1),
字段3 varchar(1),
字段4 varchar(1)
)
insert into a
select 'a','b','c','1' union
select 'a','3','f','2' union
select 'd','b','c','3' union
select 'd','3','f','4'
go
select aa.* from a as aa where (select count(*) from a where 字段1=aa.字段1 and 字段2+字段3+字段4<=aa.字段2+aa.字段3+aa.字段4)=1
字段1 字段2 字段3 字段4
---- ---- ---- ----
a 3 f 2
d 3 f 4
create table test(字段1 varchar(10),字段2 varchar(10),字段3 varchar(10),字段4 int)
insert test select 'a','b','c',1
union all select 'a','e','f',2
union all select 'd','b','c',3
union all select 'd','e','f',4
select * from test a where 字段4=
(
select top 1 字段4 from test where 字段1=a.字段1
)