表t
ID Name Type Detail
1 a 1 aaa
2 a 2 bbb
3 b 2 bbb
要求:Name相同的就显示Type小的那条记录
比如条件是空的
出来的结果是
ID Name Type Detail
1 a 1 aaa
3 b 2 bbb
ID Name Type Detail
1 a 1 aaa
2 a 2 bbb
3 b 2 bbb
要求:Name相同的就显示Type小的那条记录
比如条件是空的
出来的结果是
ID Name Type Detail
1 a 1 aaa
3 b 2 bbb
where Type = (select min(Type) from 表t where Name=t.Name)
(select name,min(type) type from t group by name) b
where a.name = b.name and a.type = b.type
type<a.type)
left join (select Name,min(type) as type from 表t group by name) b
on a.name=b.name and a.type=b.type
create table t1(
ID int identity(1,1),
Name varchar(1),
Type int,
Detail varchar(10)
)insert t1
select 'a',1,'aaa' union all
select 'a',2,'bbb' union all
select 'b',2,'bbb'select * from t1 a
where not exists(select 1 from t1 where Name=a.Name and Type<a.Type )
--result:
1 a 1 aaa
3 b 2 bbb
insert into t values(1, 'a', 1, 'aaa')
insert into t values(2, 'a', 2, 'bbb')
insert into t values(3, 'b', 2, 'bbb')
go
select a.* from t a,
(select name,min(type) type from t group by name) b
where a.name = b.name and a.type = b.type
/*
ID Name Type Detail
----------- ---------- ----------- ----------
1 a 1 aaa
3 b 2 bbb
(所影响的行数为 2 行)
*/
select a.* from t a where a.type = (select top 1 type from t where name = a.name)
/*
ID Name Type Detail
----------- ---------- ----------- ----------
1 a 1 aaa
3 b 2 bbb
(所影响的行数为 2 行)
*/
drop table t