我有桌子
ID, name, pay1, pay2
1 a, 3, 2
2 b, 12, 4
3 b, 4, 8
4 c, 8, 7
5 c, 5, 2
6 a, 7, 1
我想选择每个名称中pay1 + pay2最少的行。所以,我想得到
ID, name, pay1, pay2
1 a, 3, 2
3 b, 4, 8
5 c, 5, 2
任何想法如何在SQL Server中做到这一点?谢谢
最佳答案
使用排名功能:
with minpay as
(
select *
, payrank = row_number() over (partition by name order by pay1 + pay2, ID)
from pay
)
select ID
, name
, pay1
, pay2
from minpay
where payrank = 1
order by name
SQL Fiddle with demo。