表Performance
id name result
1 tom 100
2 jenny 80
3 tom 60
4 tom 50
5 jenny 100
表中有人名重復的記錄，請問怎么得出每人的最高成績，就像這樣：
id name result
1 tom 100
2 jenny 100
uj5u.com熱心網友回復：
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[id] NVARCHAR(MAX)
,[name] NVARCHAR(MAX)
,[result] int
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'1',N'tom',N'100')
INSERT INTO dbo.[t] VALUES(N'2',N'jenny',N'80')
INSERT INTO dbo.[t] VALUES(N'3',N'tom',N'60')
INSERT INTO dbo.[t] VALUES(N'4',N'tom',N'50')
INSERT INTO dbo.[t] VALUES(N'5',N'jenny',N'100')
------- 以上為測驗表和測驗資料 -------
select row_number() over(order by id) as rowId,id,name,result
from (
select
row_number() over(partition by name order by result desc) as rid
,*
from t
) as tt
where tt.rid=1
order by id
/*
rowId id name result
1 1 tom 100
2 5 jenny 100
*/
uj5u.com熱心網友回復：
create table Performance(id int,name varchar(10),result int)
insert into Performance
select 1,'tom',100 union all
select 2,'jenny',80 union all
select 3,'tom',60 union all
select 4,'tom',50 union all
select 5,'jenny',100
select *
from Performance a
where not exists(select 1
from Performance b
where b.id<>a.id
and b.name=a.name
and b.result>a.result)
/*
id name result
----------- ---------- -----------
1 tom 100
5 jenny 100
(2 rows affected)
*/
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/71591.html
標籤：疑難問題