求一条sql语句。。。。。。

Question

这里有2个表：ZC_AdmissionID            Adname          Adcode          Adsex
260            李磊            ll               22
261            王宏            wh               26ZC_PaymentFormPF_ID         Admission_ID       PF_Storedvalue
  7               260                 500
  8               261                 200
  9               260                 100 然后我执行这条SQL语句结果如下：SQl:select * from(select row_number()over(order by tempColumn)tempRowNumber,* from(select
top 10 tempColumn=0,* from ZC_Admission)t)tt
join ZC_PaymentForm a on ID=a.Admission_ID
AND a.PF_ID IN(SELECT max(PF_ID) FROM ZC_PaymentForm WHERE Admission_ID=a.Admission_ID)
where tempRowNumber>0
ID            Adname          Adcode          Adsex     PF_ID         Admission_ID       PF_Storedvalue
260            李磊            ll               22        9              260                 100
261            王宏            wh               26        8              261                 200      在我的SQL语句上进行修改。想要的结果如下：
ID       Adname          Adcode          Adsex     PF_ID    Admission_ID       PF_Storedvalue    ASvalue   
260      李磊            ll               22        9         260                 100              600
261      王宏            wh               26        8         261                 200              200
ASvalue   这个字段就是计算每条记录  PF_Storedvalue 之和  。

Answer 1

select * from(select row_number()over(order by tempColumn)tempRowNumber,* from(select
top 10 tempColumn=0,* from ZC_Admission)t)tt
join 
(select pf_id=max(pf_id),Admission_ID,ASvalue=sum(PF_Storedvalue) from ZC_PaymentForm group by Admission_ID)a on ID=a.Admission_ID
where tempRowNumber>0

Answer 2

select *,(select sum(PF_Storedvalue) from ZC_PaymentForm where Admission_ID=a.Admission_ID )ASvalue
from(
select row_number()over(order by tempColumn)tempRowNumber,* 
from(
select top 10 tempColumn=0,* 
from ZC_Admission
)t
)tt
join ZC_PaymentForm a on ID=a.Admission_ID
AND a.PF_ID IN(SELECT max(PF_ID) FROM ZC_PaymentForm WHERE Admission_ID=a.Admission_ID)
where tempRowNumber>0

Answer 3

--上面的少了个字段，这样：
select * from(select row_number()over(order by tempColumn)tempRowNumber,* from(select
top 10 tempColumn=0,* from ZC_Admission)t)tt
join 
(select *,ASvalue=(select sum(PF_Storedvalue) from  PaymentForm where Admission_ID=a.Admission_ID) from PaymentForm a 
where PF_ID IN(SELECT max(PF_ID) FROM ZC_PaymentForm WHERE Admission_ID=a.Admission_ID))a on ID=a.Admission_ID
where tempRowNumber>0

Answer 4

--> 测试数据：#ZC_Admission
if object_id('tempdb.dbo.#ZC_Admission') is not null drop table #ZC_Admission
create table #ZC_Admission([ID] int,[Adname] varchar(4),[Adcode] varchar(2),[Adsex] int)
insert #ZC_Admission
select 260,'李磊','ll',22 union all
select 261,'王宏','wh',26
--> 测试数据：#ZC_PaymentForm
if object_id('tempdb.dbo.#ZC_PaymentForm') is not null drop table #ZC_PaymentForm
create table #ZC_PaymentForm([PF_ID] int,[Admission_ID] int,[PF_Storedvalue] int)
insert #ZC_PaymentForm
select 7,260,500 union all
select 8,261,200 union all
select 9,260,100select * from #ZC_Admission a
inner join 
(
select max([PF_ID]) as [PF_ID] ,[Admission_ID],sum([PF_Storedvalue]) as [ASvalue] from #ZC_PaymentForm group by [Admission_ID]
) as b
on a.[ID]=b.[Admission_ID]
/*ID          Adname Adcode Adsex       PF_ID       Admission_ID ASvalue     
----------- ------ ------ ----------- ----------- ------------ ----------- 
260         李磊     ll     22          9           260          600
261         王宏     wh     26          8           261          200（所影响的行数为 2 行）*/

Answer 5

select * from(select row_number()over(order by tempColumn)tempRowNumber,* from(select
top 10 tempColumn=0,* from ZC_Admission)t)tt
join (SELECT max(PF_ID),Admission_ID, SUM(PF_Storedvalue) FROM ZC_PaymentForm GROUP BY Admission_ID) a on ID=a.Admission_ID
where tempRowNumber>0

Answer 6

借用一下楼上的测试数据。。
--> 测试数据：#ZC_Admission
if object_id('tempdb.dbo.#ZC_Admission') is not null drop table #ZC_Admission
create table #ZC_Admission([ID] int,[Adname] varchar(4),[Adcode] varchar(2),[Adsex] int)
insert #ZC_Admission
select 260,'李磊','ll',22 union all
select 261,'王宏','wh',26
--> 测试数据：#ZC_PaymentForm
if object_id('tempdb.dbo.#ZC_PaymentForm') is not null drop table #ZC_PaymentForm
create table #ZC_PaymentForm([PF_ID] int,[Admission_ID] int,[PF_Storedvalue] int)
insert #ZC_PaymentForm
select 7,260,500 union all
select 8,261,200 union all
select 9,260,100select * from(select row_number()over(order by tempColumn)tempRowNumber,* from(select
top 10 tempColumn=0,* from #ZC_Admission)t)tt
join (SELECT max(PF_ID) PF_ID,Admission_ID, SUM(PF_Storedvalue) PF_Storedvalue FROM #ZC_PaymentForm GROUP BY Admission_ID) a on ID=a.Admission_ID
where tempRowNumber>0/*
tempRowNumber        tempColumn  ID          Adname Adcode Adsex       PF_ID       Admission_ID PF_Storedvalue
-------------------- ----------- ----------- ------ ------ ----------- ----------- ------------ --------------
1                    0           260         李磊     ll     22          9           260          600
2                    0           261         王宏     wh     26          8           261          200(2 row(s) affected)
*/

Answer 7

谢谢 楼上2位的回答  但是都有个问题，就是 为什么ID为 260 和261 取的值是一样的呢，
应该是 ：ID     ASvalue   
        260      600
        261      200