--测试数据 if not object_id(N'Tempdb..#a') is null drop table #a Go Create table #a([id] int,[amount] int) Insert #a select 1,100 union all select 2,10 union all select 3,20 union all select 2,30 union all select 1,40 union all select 3,50 union all select 4,100 union all select 4,20 Go --测试数据结束 ;WITH cte AS( SELECT id , SUM(amount) AS amount FROM #a GROUP BY id ) SELECT cte.* FROM cte JOIN ( SELECT MAX(amount) AS amount FROM cte ) t ON cte.amount = t.amount
if not object_id(N'Tempdb..#a') is null drop table #a Go Create table #a(ID int,amount int) Insert #a select 1 ,100 union all select 2 ,10 union all select 3 ,20 union all select 2 ,30 union all select 1 ,40 union all select 3 ,50 union all select 4 ,100 union all select 4 ,20 Go select top 1 ID,sum(amount) as sum_amount from #a group by ID order by sum(amount) ID sum_amount 1 2 40
Select Top 1 ID,MAX(amount) From ( Select ID,Sum(amount) as amount From 表a Group By ID ) Order By amount
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[amount] int)
Insert #a
select 1,100 union all
select 2,10 union all
select 3,20 union all
select 2,30 union all
select 1,40 union all
select 3,50 union all
select 4,100 union all
select 4,20
Go
--测试数据结束
;WITH cte AS(
SELECT id ,
SUM(amount) AS amount
FROM #a
GROUP BY id
)
SELECT cte.*
FROM cte
JOIN ( SELECT MAX(amount) AS amount
FROM cte
) t ON cte.amount = t.amount
if not object_id(N'Tempdb..#a') is null drop table #a
Go
Create table #a(ID int,amount int)
Insert #a
select 1 ,100 union all
select 2 ,10 union all
select 3 ,20 union all
select 2 ,30 union all
select 1 ,40 union all
select 3 ,50 union all
select 4 ,100 union all
select 4 ,20
Go
select top 1 ID,sum(amount) as sum_amount from #a
group by ID
order by sum(amount) ID sum_amount
1 2 40
From (
Select ID,Sum(amount) as amount From 表a Group By ID
)
Order By amount