update TableName set 余额=余额+isnull(借方,0)-isnull(贷方,0)
update TableName set 余额=余额+借方-贷方
select *,(select 100+sum(isnull(借方,0)-isnull(贷方,0)) 余额 from 表 where 编号<=a.编号) from 表 a或: select *,(select 100+sum(isnull(借方,0)-isnull(贷方,0)) 余额 from 表 where 时间<=a.时间) from 表 a--------上面的100是期初数,编号或时间是能够唯一标识你记录顺序的关键列
declare @jf decimal(18,2),@df decimal(18,2),@Ye decimal(18,2) Declare Mxz_Cursor Cursor For (Select 借方,贷方 From aa) Open Mxz_Cursor Fetch Next From Mxz_Cursor Into @jf,@df Set @Ye=100 While (@@Fetch_Status=0) Begin if @jf=0 and @df=0 set @Ye=@Ye Else Begin if @jf<>0 Set @Ye=@Ye-@Jf else Set @Ye=@Ye+@df end update aa set 余额=@Ye where CURRENT OF Mxz_cursor Fetch Next From Mxz_Cursor Into @Jf,@Df End Close Mxz_Cursor Deallocate Mxz_Cursor
我如何算出并列一个表给老总,没有余额这个字段,如何用select 做?????????????
不是一句话搞定了吗?select *,(select 100+sum(isnull(借方,0)-isnull(贷方,0)) 余额 from 表 where 编号<=a.编号) from 表 a
SELECT GLRDTL.LINE_NO, GLRDTL.MODEL, GLRDTL.VOUCHER_NO, GLRDTL.YEAR, GLRDTL.PERIOD, GLRDTL.VOUCHER_DATE, GLRDTL.ACCOUNT_NO, GLRDTL.PARTICULARS, GLRDTL.LAYER_LEVEL, GLRDTL.NORMAL_SIGN AS 期初 , GLRDTL.DEBIT_AMT AS 借 , GLRDTL.CREDIT_AMT AS 贷 , GLRDTL.REMAIN_AMT, GLRDTL.ACC_PERIOD, GLRDTL.BASE_AMT, GLRDTL.POSTED, CASE WHEN GLRDTL.MODEL = 'A' AND GLRDTL.BASE_AMT = 0 THEN '平' WHEN GLRDTL.MODEL = 'B' AND GLRDTL.BASE_AMT > 0 THEN '借' WHEN GLRDTL.MODEL = 'B' AND GLRDTL.BASE_AMT < 0 THEN '贷' END OPT, CASE WHEN MODEL='A' THEN REMAIN_AMT WHEN NORMAL_SIGN = 'Credit' THEN SELECT (REMAIN_AMT + ISNULL(CREDIT_AMT,0) - ISNULL(DEBIT_AMT,0))FROM GLRDTL WHEN NORMAL_SIGN = 'Debit' THEN REMAIN_AMT - CREDIT_AMT + DEBIT_AMT (BASE_AMT) END AMT FROM GLRDTL WHERE ACCOUNT_NO = '204000101' ORDER BY GLRDTL.ACCOUNT_NO,GLRDTL.MODEL,GLRDTL.YEAR,GLRDTL.PERIOD,GLRDTL.VOUCHER_DATE REMAIN_AMT是期初 CREDIT_AMT是借方 DEBIT_AMT是贷方我要实现的是AMT是余额的变量如何做
如果没有能唯一标识每条记录顺序的列的话那用:select identity(int,1,1) 编号,* into #temp from 表 ---为表加上编号select *,(select 100+sum(isnull(借方,0)-isnull(贷方,0)) from #temp where 编号<=a.编号) 余额 from #temp a
SELECT GLRDTL.MODEL, GLRDTL.YEAR, GLRDTL.ACCOUNT_NO, GLRDTL.DEBIT_AMT AS 借 , GLRDTL.CREDIT_AMT AS 贷 , GLRDTL.REMAIN_AMT AS 期初
AMT (这个在表中没有这个字段,因此要由程序生成,则为期初+借-贷 如何做????) FROM ACCOUNT
你的期初每条记录都有吗? SELECT MODEL, YEAR, ACCOUNT_NO, DEBIT_AMT AS 借 , CREDIT_AMT AS 贷 , REMAIN_AMT AS 期初, REMAIN_AMT+GLRDTL.DEBIT_AMT-GLRDTL.CREDIT_AMT as 余 FROM ACCOUNT
SELECT MODEL, YEAR, ACCOUNT_NO, NORMAL_SIGN AS 科目类型, DEBIT_AMT AS 借 , CREDIT_AMT AS 贷 , REMAIN_AMT AS 期初,
CASE
WHEN NORMAL_SIGN = 'Credit' THEN REMAIN_AMT + CREDIT_AMT -DEBIT_AMT WHEN NORMAL_SIGN = 'Debit' THEN REMAIN_AMT - CREDIT_AMT + DEBIT_AMT END AMT FROM TABLE 这个AMT要他自动生成并且有一些条件的。如何做这样我的语句是错的你给个正确的
语法是对的呀:SELECT MODEL, YEAR, ACCOUNT_NO, NORMAL_SIGN AS 科目类型, DEBIT_AMT AS 借 , CREDIT_AMT AS 贷 , REMAIN_AMT AS 期初, CASE WHEN NORMAL_SIGN = 'Credit' THEN REMAIN_AMT + CREDIT_AMT -DEBIT_AMT WHEN NORMAL_SIGN = 'Debit' THEN REMAIN_AMT - CREDIT_AMT + DEBIT_AMT END AMT FROM [TABLE] 是逻辑错了吗?? 列举两条示例字段值!
select identity(int,1,1) 编号,* into #temp from ACCOUNTselect *,(select sum(GLRDTL.REMAIN_AMT+isnull(GLRDTL.DEBIT_AMT,0)-isnull(GLRDTL.CREDIT_AMT,0)) from #temp where 编号<=a.编号) 余额 from #temp a drop table #temp
select *,(select 100+sum(isnull(借方,0)-isnull(贷方,0)) 余额 from 表 where 时间<=a.时间) from 表 a--------上面的100是期初数,编号或时间是能够唯一标识你记录顺序的关键列
Declare Mxz_Cursor Cursor For (Select 借方,贷方 From aa)
Open Mxz_Cursor
Fetch Next From Mxz_Cursor Into @jf,@df
Set @Ye=100
While (@@Fetch_Status=0)
Begin
if @jf=0 and @df=0
set @Ye=@Ye
Else
Begin
if @jf<>0
Set @Ye=@Ye-@Jf
else
Set @Ye=@Ye+@df
end
update aa set 余额=@Ye where CURRENT OF Mxz_cursor
Fetch Next From Mxz_Cursor Into @Jf,@Df
End
Close Mxz_Cursor
Deallocate Mxz_Cursor
GLRDTL.MODEL,
GLRDTL.VOUCHER_NO,
GLRDTL.YEAR,
GLRDTL.PERIOD,
GLRDTL.VOUCHER_DATE,
GLRDTL.ACCOUNT_NO,
GLRDTL.PARTICULARS,
GLRDTL.LAYER_LEVEL,
GLRDTL.NORMAL_SIGN AS 期初 ,
GLRDTL.DEBIT_AMT AS 借 ,
GLRDTL.CREDIT_AMT AS 贷 ,
GLRDTL.REMAIN_AMT,
GLRDTL.ACC_PERIOD,
GLRDTL.BASE_AMT,
GLRDTL.POSTED,
CASE
WHEN GLRDTL.MODEL = 'A' AND GLRDTL.BASE_AMT = 0 THEN '平'
WHEN GLRDTL.MODEL = 'B' AND GLRDTL.BASE_AMT > 0 THEN '借'
WHEN GLRDTL.MODEL = 'B' AND GLRDTL.BASE_AMT < 0 THEN '贷'
END OPT,
CASE
WHEN MODEL='A' THEN REMAIN_AMT
WHEN NORMAL_SIGN = 'Credit' THEN SELECT (REMAIN_AMT + ISNULL(CREDIT_AMT,0) - ISNULL(DEBIT_AMT,0))FROM GLRDTL
WHEN NORMAL_SIGN = 'Debit' THEN REMAIN_AMT - CREDIT_AMT + DEBIT_AMT
(BASE_AMT)
END AMT
FROM GLRDTL WHERE ACCOUNT_NO = '204000101'
ORDER BY GLRDTL.ACCOUNT_NO,GLRDTL.MODEL,GLRDTL.YEAR,GLRDTL.PERIOD,GLRDTL.VOUCHER_DATE
REMAIN_AMT是期初
CREDIT_AMT是借方
DEBIT_AMT是贷方我要实现的是AMT是余额的变量如何做
GLRDTL.YEAR,
GLRDTL.ACCOUNT_NO,
GLRDTL.DEBIT_AMT AS 借 ,
GLRDTL.CREDIT_AMT AS 贷 ,
GLRDTL.REMAIN_AMT AS 期初
AMT (这个在表中没有这个字段,因此要由程序生成,则为期初+借-贷 如何做????)
FROM ACCOUNT
SELECT MODEL,
YEAR,
ACCOUNT_NO,
DEBIT_AMT AS 借 ,
CREDIT_AMT AS 贷 ,
REMAIN_AMT AS 期初,
REMAIN_AMT+GLRDTL.DEBIT_AMT-GLRDTL.CREDIT_AMT as 余
FROM ACCOUNT
YEAR,
ACCOUNT_NO,
NORMAL_SIGN AS 科目类型,
DEBIT_AMT AS 借 ,
CREDIT_AMT AS 贷 ,
REMAIN_AMT AS 期初,
CASE
WHEN NORMAL_SIGN = 'Credit' THEN REMAIN_AMT + CREDIT_AMT -DEBIT_AMT
WHEN NORMAL_SIGN = 'Debit' THEN REMAIN_AMT - CREDIT_AMT + DEBIT_AMT
END AMT
FROM TABLE
这个AMT要他自动生成并且有一些条件的。如何做这样我的语句是错的你给个正确的
YEAR,
ACCOUNT_NO,
NORMAL_SIGN AS 科目类型,
DEBIT_AMT AS 借 ,
CREDIT_AMT AS 贷 ,
REMAIN_AMT AS 期初,
CASE
WHEN NORMAL_SIGN = 'Credit' THEN REMAIN_AMT + CREDIT_AMT -DEBIT_AMT
WHEN NORMAL_SIGN = 'Debit' THEN REMAIN_AMT - CREDIT_AMT + DEBIT_AMT
END AMT
FROM [TABLE]
是逻辑错了吗??
列举两条示例字段值!
---------- ---------- -------------------- -------------------- -------------------- ----------------------
204000101 Credit NULL NULL 1313562.6000 NULL
204000101 Credit 90000.0000 .0000 .0000 -90000.0000
204000101 Credit .0000 50250.0000 .0000 50250.0000
204000101 Credit 282765.0000 .0000 .0000 -282765.0000
AMT没有进行算
---------- ---------- ------------ ------------- ----------------- -----------
204000101 Credit NULL NULL 1313562.6000 NULL
204000101 Credit 90000.0000 .0000 .0000 -90000.0000
204000101 Credit .0000 50250.0000 .0000 50250.0000
204000101 Credit 282765.0000 .0000 .0000 -282765.0000amt没有算只是成了负数了,是错的他没能算
drop table #temp