SQL语句求助

[ikgan]

Declare @LsBH Varchar(12)
DECLARE C_Finance CURSOR FOR
SELECT [单位编号] FROM [T_往来帐册] where [收付标记]=Convert(Int,@LiCode) and [操作人员] = @user
OPEN C_Finance
FETCH NEXT FROM C_Finance Into @LsBH
WHILE @@FETCH_STATUS = 0
BEGIN
Update [T_往来帐册] Set
[增加金额]=isnull((Select Sum([发生金额]) From [T_往来帐单]
where [单位编号]=@LsBH and [收付标记]=@LiCode and ([帐单日期] between @BeginDay and @EndDay)),0),
[付款金额]=isnull((Select Sum([发生金额]) From [T_往来记录]
where [单位编号]=@LsBH and [收付标记]=@LiCode and ([发生日期] between @BeginDay and @EndDay)),0),
[期末剩余]=(isnull((Select Sum([发生金额]) From [T_往来帐单]
where [单位编号]=@LsBH and [收付标记]=@LiCode and ([帐单日期]<=@EndDay)),0) -isnull((Select Sum([发生金额]) From [T_往来记录]
where [单位编号]=@LsBH and [收付标记]=@LiCode and ([发生日期]<=@EndDay)),0))
where [T_往来帐册].[单位编号]=@LsBH and [T_往来帐册].[收付标记]=@LiCode and [T_往来帐册].[操作人员] = @user

FETCH NEXT FROM C_Finance Into @LsBH
END
CLOSE C_Finance
DEALLOCATE C_Finance

上面这段SQL，有没有办法不用游标实现？

[hellowork]

测试前请先备份或在测试表中测试:

UPDATE a SET
[增加金额]=isnull((Select Sum([发生金额]) From [T_往来帐单]
where ([帐单日期] between @BeginDay and @EndDay)),0),
[付款金额]=isnull((Select Sum([发生金额]) From [T_往来记录]
where ([发生日期] between @BeginDay and @EndDay)),0),
[期末剩余]=(isnull((Select Sum([发生金额]) From [T_往来帐单]
where ([帐单日期]<=@EndDay)),0)
-
isnull((Select Sum([发生金额]) From [T_往来记录]
where ([发生日期]<=@EndDay)),0))
FROM [T_往来帐册] AS a
WHERE a.[收付标记]=Convert(Int,@LiCode) and a.[操作人员] = @user

[ikgan]

楼上的肯定是不行的

[hellowork]

抱歉,更正一下,子查询中忘了关联条件:
UPDATE a SET
[增加金额]=isnull((Select Sum([发生金额]) From [T_往来帐单]
where ([单位编号]=a.[单位编号] and [收付标记]=a.[收付标记] and [帐单日期] between @BeginDay and @EndDay)),0),
[付款金额]=isnull((Select Sum([发生金额]) From [T_往来记录]
where ([单位编号]=a.[单位编号] and [收付标记]=a.[收付标记] and [发生日期] between @BeginDay and @EndDay)),0),
[期末剩余]=(isnull((Select Sum([发生金额]) From [T_往来帐单]
where ([单位编号]=a.[单位编号] and [收付标记]=a.[收付标记] and [帐单日期]<=@EndDay)),0)
-
isnull((Select Sum([发生金额]) From [T_往来记录]
where ([单位编号]=a.[单位编号] and [收付标记]=a.[收付标记] and [发生日期]<=@EndDay)),0))
FROM [T_往来帐册] AS a
WHERE a.[收付标记]=Convert(Int,@LiCode) and a.[操作人员] = @user

[hellowork]

或者这样试试:
UPDATE a SET
[增加金额]= b.[增加金额]
[付款金额]= c.[付款金额]
[期末剩余]= isnull(d.[发生金额],0) - isnull(e.[发生金额],0)
FROM [T_往来帐册] AS a
LEFT JOIN
(select [单位编号],Sum([发生金额]) as [增加金额] From [T_往来帐单]
where [单位编号]=a.[单位编号] and [收付标记]=a.[收付标记] and [帐单日期] between @BeginDay and @EndDay
group by [单位编号]) AS b
ON a.[单位编号] = b.[单位编号]
LEFT JOIN
(select [单位编号],Sum([发生金额]) as [付款金额] From [T_往来记录]
where [单位编号]=a.[单位编号] and [收付标记]=a.[收付标记] and [发生日期] between @BeginDay and @EndDay
group by [单位编号]) AS c
ON a.[单位编号] = c.[单位编号]
LEFT JOIN
(Select [单位编号],Sum([发生金额]) as [发生金额] From [T_往来帐单]
where [单位编号]=a.[单位编号] and [收付标记]=a.[收付标记] and [帐单日期]<=@EndDay
group by [单位编号]) AS d
ON a.[单位编号] = d.[单位编号]
LEFT JOIN
(Select [单位编号],Sum([发生金额]) as [发生金额] From [T_往来记录]
where [单位编号]=a.[单位编号] and [收付标记]=a.[收付标记] and [发生日期]<=@EndDay
group by [单位编号]) AS e
ON a.[单位编号] = e.[单位编号]
WHERE a.[收付标记]=Convert(Int,@LiCode) and a.[操作人员] = @user