sql 多条数据记录取最大值

[ycwww]

数据结构如下：

table

ID Uname Price BuyDate
1 张三 180 2017-12-1
2 张三 280 2017-12-7
3 李四 480 2017-12-10
4 李四 280 2017-12-11
5 王武 280 2017-12-1
6 王武 880 2017-12-11
7 王武 380 2017-12-15

要求得到结果为 (取所有记录的price中的最大值，唯一一条数据列出)：

ID Uname Price BuyDate
2 张三 280 2017-12-7
3 李四 480 2017-12-10
6 王武 880 2017-12-11

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[Uname] nvarchar(22),[Price] int,[BuyDate] Date)
Insert #T
select 1,N'张三',180,'2017-12-1' union all
select 2,N'张三',280,'2017-12-7' union all
select 3,N'李四',480,'2017-12-10' union all
select 4,N'李四',280,'2017-12-11' union all
select 5,N'王武',280,'2017-12-1' union all
select 6,N'王武',880,'2017-12-11' union all
select 7,N'王武',380,'2017-12-15'
Go
--测试数据结束
SELECT  ID ,
        Uname ,
        Price ,
        BuyDate
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY Uname ORDER BY Price DESC ) AS num
          FROM      #T
        ) t
WHERE   num = 1

[吉普赛的歌]

row_number 最容易理解

[中国风]

在另一贴已回复 顺序加上，Uname分组，取最大Price，最大BuyDate，最大ID 优先取

SELECT * FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY Uname ORDER BY Price DESC,BuyDate DESC,ID DESC) AS RN FROM tabName ) AS T WHERE RN=1

其它方法可参照 http://bbs.csdn.net/topics/240034273

[RINK_1]

select * 
from table A
where not exists (select 1 from table where Uname=A.Uname and Price>A.Price)