MSSQL 多条件去重

[qq_37400227]

图中为所查结果,我想在此基础上,以a和b为条件去重,查询出d为最大的那一列
即结果为2,4,6,7列

[RINK_1]

SELECT * FROM TABLE_XXX A
WHERE NOT EXISTS 
(SELECT 1 FROM TABLE_XXX WHERE A.a=a AND A.b=b and d>A.d)

[weixin_44723853]

select *
from
(
select *,row_number() over(partition by a,b order by d desc) RowID
from Table
)a
where RowID = 1

[qq_25073223]

建议使用 row_number方法进行去重
row_number去重方法

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([a] int,[b] int)
Insert #T
select 3,2 union all
select 3,2 union all
select 369,2 union all
select 369,2 union all
select 381,1 union all
select 381,1 union all
select 3807,1
Go
--测试数据结束
SELECT a,b FROM (
Select *,ROW_NUMBER()OVER(PARTITION BY a ORDER BY b DESC) rn from #T
)t
WHERE rn = 1

[qq_37400227]

引用 1 楼 RINK_1 的回复:

SELECT * FROM TABLE_XXX A
WHERE NOT EXISTS 
(SELECT 1 FROM TABLE_XXX WHERE A.a=a AND A.b=b and d>A.d)

感谢,只有你看见我想表达的取d列最大的那一条