--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (pid int,uid varchar(1),gold int)
insert into #tb
select 10,'A',10 union all
select 10,'B',12 union all
select 5,'C',6 union all
select 5,'B',8 union all
select 1,'A',5 union all
select 1,'C',10select * from #tb t
where not exists(select * from #tb where pid=t.pid and gold>t.gold)pid uid gold
----------- ---- -----------
10 B 12
5 B 8
1 C 10(3 行受影响)
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (pid int,uid varchar(1),gold int)
insert into #tb
select 10,'A',10 union all
select 10,'B',12 union all
select 5,'C',6 union all
select 5,'B',8 union all
select 1,'A',5 union all
select 1,'C',10select * from #tb t
where not exists(select * from #tb where pid=t.pid and gold>t.gold)pid uid gold
----------- ---- -----------
10 B 12
5 B 8
1 C 10(3 行受影响)
where gold=(select max(gold) from #tb where pid=t.pid)pid uid gold
----------- ---- -----------
10 B 12
5 B 8
1 C 10
if object_id('tb')>0
drop table tb
create table tb
(
pid int,
uid varchar(10),
gold int
)insert into tb
select 10 ,'A', 10
union all
select 10 ,'B', 12
union all
select 5 ,'C', 6
union all
select 5, 'B', 8
union all
select 1, 'A', 5
union all
select 1, 'C', 10select pid,(select top 1 b.uid from tb as b
where a.pid = b.pid
order by b.gold desc),max(gold)
from tb as a
group by pid结果
1 C 10
5 B 8
10 B 12
select pid,(select top 1 b.uid from tb as b
where a.pid = b.pid
order by b.gold desc),max(gold)
from tb as a
group by pid
order by pid desc
结果10 B 12
5 B 8
1 C 10
select pid,uid ,gold from tb a
where not exists (select * from tb b where a.pid = b.pid and a.gold < b.gold )