ID Name score
1 zhangsan 90
2 lisi 78
3 wangwu 76
4 zhangsan 86
查询结果 要 不重复的所有数据 重复数据 按照score 倒序 取出第一个结果如下ID Name score
1 zhangsan 90
2 lisi 78
3 wangwu 76
求SQL
1 zhangsan 90
2 lisi 78
3 wangwu 76
4 zhangsan 86
查询结果 要 不重复的所有数据 重复数据 按照score 倒序 取出第一个结果如下ID Name score
1 zhangsan 90
2 lisi 78
3 wangwu 76
求SQL
insert into tb values(1 ,'zhangsan' ,90)
insert into tb values(2 ,'lisi' ,78)
insert into tb values(3 ,'wangwu' ,76)
insert into tb values(4 ,'zhangsan' ,86)
goselect t.* from tb t where score = (select max(score) from tb where name = t.name) order by t.id
/*
ID Name score
----------- -------------------- -----------
1 zhangsan 90
2 lisi 78
3 wangwu 76(所影响的行数为 3 行)
*/select t.* from tb t where not exists (select 1 from tb where name = t.name and score > t.score) order by t.id
/*
ID Name score
----------- -------------------- -----------
1 zhangsan 90
2 lisi 78
3 wangwu 76(所影响的行数为 3 行)
*/
drop table tb
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
ID int identity(1,1),
Name varchar(10),
score int
)
go
insert into tb (Name,score)
select 'zhangsan',90 union all
select 'lisi',78 union all
select 'wangwu',76 union all
select 'zhangsan',86
go
select * from tb t1 where not exists(select 1 from tb where name=t1.name and score>t1.score)
go
/*
ID Name score
----------- ---------- -----------
1 zhangsan 90
2 lisi 78
3 wangwu 76(3 行受影响)
*/
create table test(id int,name varchar(50),score int);insert into test
select 1,'zhangsan',90
union
select 2,'lisi',78
union
select 3,'wangwu',76
union
select 4,'zhangsan',86select * from test a where
not exists(select 1 from test b where a.name=b.name and a.score<b.score)
/*
create table test
(
ID int not null,
Name nvarchar(20) not null,
score int not null
);
insert into test(ID,Name,score) values(1,'zhangsan',90);
insert into test(ID,Name,score) values(2,'lisi',78);
insert into test(ID,Name,score) values(3,'wangwu',76);
insert into test(ID,Name,score) values(4,'zhangsan',86);
*/
select *
from
(select *,ROW_NUMBER() OVER(PARTITION BY test.Name ORDER BY test.score DESC) as ordernum from test) as t
where t.ordernum = 1
order by t.score desc;
/*
create table test
(
ID int not null,
Name nvarchar(20) not null,
score int not null
);
insert into test(ID,Name,score) values(1,'zhangsan',90);
insert into test(ID,Name,score) values(2,'lisi',78);
insert into test(ID,Name,score) values(3,'wangwu',76);
insert into test(ID,Name,score) values(4,'zhangsan',86);
*/
select t.ID,t.Name,t.score
from
(select *,ROW_NUMBER() OVER(PARTITION BY test.Name ORDER BY test.score DESC) as ordernum from test) as t
where t.ordernum = 1
order by t.score desc;