大家能否看看这个SQL有可能实现吗？

[jerry_huang]

源表A：
Name Position
Jerry IT
Tom IT
Jack IT
Sherry HR
Diana HR
Echo HR

源表B：
Position Budget
IT 4
HR 2

连接后的表C：
Name Position Budget
Jerry IT 4
Tom IT 0
Jack IT 0
Sherry HR 2
Diana HR 0
Echo HR 0

也就是说连接两个表以后，我只想第一条记录取到另一个表的值，也就是说只有第一条IT记录取得Budget=4，而其它都取0，HR也是一样，第一条取得2，其余取0，SQL能这样实现吗？

我只有十几分了，只能给这么多了，不好意思！

[a_stupid_boy]

好怪的需求啊，搞不明白

[playyuer]

select *
,(select sum(Budget)
from b
where Position = a.Position
and a.Name = (select max(name) from a c where Position = a.Position)
)
from A

[zarge]

select A.name, A.position, case when A.name = (select top 1 name from A where position = B.position) then B.budget else 0 end from A inner join B on A.position = B.position

[playyuer]

select *
,isnull((select sum(Budget)
from b
where Position = a.Position
and a.Name = (select top 1 name
from a c
where Position = a.Position
order by name),0)
)
from A


select *
,isnull((select sum(Budget)
from b
where Position = a.Position
and a.Name = (select max(name) from a c where Position = a.Position)
),0)
from A