--> 测试数据: @t declare @t table (ID int,name varchar(4),grade int) insert into @t select 1,'张三',85 union all select 2,'李四',83 union all select 3,'张三',90 union all select 4,'王五',88 union all select 5,'李四',89 union all select 6,'王五',91 union all select 7,'张三',89 union all select 8,'王五',84select * from @t a where grade in(select max(grade) from @t where name=a.name group by name)
select * from @t a where not exists(select 1 from @t where name=a.name and grade>a.grade)
--借用测试数据declare @t table (ID int,name varchar(4),grade int) insert into @t select 1,'张三',85 union all select 2,'李四',83 union all select 3,'张三',90 union all select 4,'王五',88 union all select 5,'李四',89 union all select 6,'王五',91 union all select 7,'张三',89 union all select 8,'王五',84select * from @t T where not exists(select 1 from @t where name=T.name and grade >T.grade)
declare @t table (ID int,name varchar(4),grade int) insert into @t select 1,'张三',85 union all select 2,'李四',83 union all select 3,'张三',90 union all select 4,'王五',88 union all select 5,'李四',89 union all select 6,'王五',91 union all select 7,'张三',89 union all select 8,'王五',84select * from @t T where not exists(select 1 from @t where name=T.name and grade >T.grade) 是属于SQL数据查询问题啊
select * from tb a where not exists(select 1 from tb b where a.name=b.name and a.grade<b.grade)select * from tb a where a.grade in (select max(b.grade) from tb b where a.name=b.name )select * from tb a where a.grade = (select max(b.grade) from tb b where a.name=b.name )
--> 测试数据: @t
declare @t table (ID int,name varchar(4),grade int)
insert into @t
select 1,'张三',85 union all
select 2,'李四',83 union all
select 3,'张三',90 union all
select 4,'王五',88 union all
select 5,'李四',89 union all
select 6,'王五',91 union all
select 7,'张三',89 union all
select 8,'王五',84select * from @t a
where grade in(select max(grade) from @t where name=a.name group by name)
select * from @t a
where not exists(select 1 from @t where name=a.name and grade>a.grade)
--借用测试数据declare @t table (ID int,name varchar(4),grade int)
insert into @t
select 1,'张三',85 union all
select 2,'李四',83 union all
select 3,'张三',90 union all
select 4,'王五',88 union all
select 5,'李四',89 union all
select 6,'王五',91 union all
select 7,'张三',89 union all
select 8,'王五',84select * from @t T where not exists(select 1 from @t where name=T.name and grade >T.grade)
insert into @t
select 1,'张三',85 union all
select 2,'李四',83 union all
select 3,'张三',90 union all
select 4,'王五',88 union all
select 5,'李四',89 union all
select 6,'王五',91 union all
select 7,'张三',89 union all
select 8,'王五',84select * from @t T where not exists(select 1 from @t where name=T.name and grade >T.grade)
是属于SQL数据查询问题啊
where not exists(select 1 from tb b where a.name=b.name and a.grade<b.grade)select * from tb a
where a.grade in (select max(b.grade) from tb b where a.name=b.name )select * from tb a
where a.grade = (select max(b.grade) from tb b where a.name=b.name )