求一SQL函数，高手请进！在线等
表tbHU中有以下数据：xxxx为所求
HUCode Amount LineNo CostCtrCde
N0001 1200 1 001
N0001 200 2 002
N0001 xxxx 3 001 --这里的xxxx取LineNo=1的Amount即1200
N0001 xxxx 4 002 --同理，这里的xxxx取LineNo=2的Amount
--以上为第一种情况的数据LineNo连续，已知Amount的CostCtrCde不相同
N0002 200 1 001
N0002 300 3 002
N0002 500 4 003
N0002 xxxx 5 001
N0002 xxxx 6 002
N0002 xxxx 7 003
--以上为第二种情况的数据LineNo不连续，已知Amount的CostCtrCde不相同
N0003 1300 1 001
N0003 200 2 001
N0003 300 3 002
N0003 500 4 003
N0003 xxxx 5 001 --这里的xxxx取LineNo=1与LineNo=2的Amount的和
N0003 xxxx 6 002
N0003 xxxx 7 003
--以上为第三种情况的数据LineNo不连续，已知Amount的CostCtrCde有相同
函数要求 参数为HUCode，LineNo。
求高手指点！
[解决办法]
LineNo？行号？
[解决办法]
说实话 我没明白LZ意思...
select sum(Amount) from table1
where HUCode=@HUCode and
CostCtrCde=(select CostCtrCde from table1 where [LineNo]=@LineNo and HUCode=@HUCode)
按字面来说 这样不就可以了?
[解决办法]
看的比较晕。。不太明白啥意思``
[解决办法]
create table test(HUCode varchar(10),Amount int,[LineNo] int,CostCtrCde varchar(5))
insert test select 'N0001 ',1200,1, '001 '
insert test select 'N0001 ',200,2, '002 '
insert test select 'N0001 ',0,3, '001 '
insert test select 'N0001 ',0,4, '002 '
insert test select 'N0002 ',200,1, '001 '
insert test select 'N0002 ',300,3, '002 '
insert test select 'N0002 ',500,4, '003 '
insert test select 'N0002 ',0,5, '001 '
insert test select 'N0002 ',0,6, '002 '
insert test select 'N0002 ',0,7, '003 '
insert test select 'N0003 ',300,1, '001 '
insert test select 'N0003 ',200,2, '001 '
insert test select 'N0003 ',300,3, '002 '
insert test select 'N0003 ',500,4, '003 '
insert test select 'N0003 ',0,5, '001 '
insert test select 'N0003 ',0,6, '002 '
insert test select 'N0003 ',0,7, '003 '
update a set a.amount=b.amount from test a,(
select b.hucode,amount=sum(b.amount),b.costctrcde from test a,test b
where a.hucode=b.hucode and a.costctrcde=b.costctrcde and a.amount <> b.amount and b.amount <> 0
group by b.hucode,b.costctrcde)b where a.hucode=b.hucode and a.amount=0 and a.costctrcde=b.costctrcde
[解决办法]
有点小问题貌似
楼上的
[解决办法]
---创建存储过程
Create Proc Pro_Sum
@HUCode Varchar(10),
@LineNo int
As
Begin
Update A
Set Amount=(Select
Sum(Amount)
From tbHU
Where HUCode=A.HUCode And CostCtrCde=A.CostCtrCde And
[LineNo] <A.[LineNo]
Group By HUCode)
From tbHU A
Where HUCode=@HUCode And [LineNO]=@LineNO
End
GO
---调用存储过程
Exec Pro_Sum 'N0001 ',5
