22,209
社区成员
发帖
与我相关
我的任务
分享
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
SELECT *
FROM #T a
Where Price=(Select max(price) From #T b where a.Uname=b.Uname)
SELECT * FROM #t a WHERE NOT EXISTS (
SELECT 1 FROM #t b WHERE a.Uname=b.Uname AND a.Price<b.Price
)
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t(ID INT,Uname NVARCHAR(100), Price MONEY,BuyDate DATE)
INSERT INTO #t
SELECT 1,N'张三',180,'2017-12-1' UNION
SELECT 2,N'张三',280,'2017-12-7' UNION
SELECT 3,N'李四',480,'2017-12-10' UNION
SELECT 4,N'李四',280,'2017-12-11' UNION
SELECT 5,N'王武',280,'2017-12-1' UNION
SELECT 6,N'王武',880,'2017-12-11' UNION
SELECT 7,N'王武',380,'2017-12-15'
SELECT * FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY Uname ORDER BY Price DESC ) AS rn FROM #t
) AS t WHERE t.rn=1
ID Uname Price BuyDate
2 张三 280.00 2017-12-07
3 李四 480.00 2017-12-10
6 王武 880.00 2017-12-11