SELECT DJBM, DJRQ, YJJE, SJJE, SUM(YJJE) - SUM(SJJE) AS Expr1
FROM CR_DZGL GROUP BY DJBM, DJRQ, YJJE, SJJE这个语句的 djbm 是单据编码,djrq 是日期 yjje 应结款,sjje 实结款
SUM(YJJE) - SUM(SJJE) AS Expr1 这个是未结金额,但是我想在后面加一个累计未结的要怎么做是当前单据日期之前所有未结的金额
FROM CR_DZGL GROUP BY DJBM, DJRQ, YJJE, SJJE这个语句的 djbm 是单据编码,djrq 是日期 yjje 应结款,sjje 实结款
SUM(YJJE) - SUM(SJJE) AS Expr1 这个是未结金额,但是我想在后面加一个累计未结的要怎么做是当前单据日期之前所有未结的金额
FROM CR_DZGL
GROUP BY DJBM, DJRQ, YJJE, SJJE
having SUM(YJJE) - SUM(SJJE) AS total
累计未结=(select SUM(YJJE) - SUM(SJJE) from CR_DZGL where DJBM=t.DJBM and DJRQ<=t.DJRQ)
FROM CR_DZGL t
GROUP BY DJBM, DJRQ
;with cte as
(SELECT id=row_number()over(order by getdate()), DJBM, DJRQ, YJJE, SJJE, SUM(YJJE) - SUM(SJJE) AS Expr1
FROM CR_DZGL GROUP BY DJBM, DJRQ, YJJE, SJJE
)
select DJBM, t1.DJRQ, t1.YJJE, t1.SJJE,t1.Expr1,TotalExpr1=sum(t2.Expr1)
from cte t1,cte t2
where t1.id>=t2.id
group by t1.DJBM, t1.DJRQ, t1.YJJE, t1.SJJE,t1.Expr1
as
(
SELECT DJBM, DJRQ, YJJE, SJJE, SUM(YJJE) - SUM(SJJE) AS Expr1,
FROM CR_DZGL GROUP BY DJBM, DJRQ, YJJE, SJJE
) select *
(
select sum(expr1) from cte as b where b.djbm<=a.djbm
) as expr2
from cte as a
大家的都不行呀
累计未结=(select SUM(YJJE) - SUM(SJJE) from CR_DZGL where DJBM=t.DJBM and DJRQ<=t.DJRQ)
FROM CR_DZGL t
GROUP BY DJBM, DJRQ这个是可以,但是我想再加一条 前单未结 = 累计未结 - Expr1 怎么不行呀
SELECT DJBM, DJRQ, YJJE, SJJE, SUM(YJJE) - SUM(SJJE) AS Expr1,
累计未结=(select SUM(YJJE) - SUM(SJJE) from CR_DZGL where DJBM=t.DJBM and DJRQ<t.DJRQ)
FROM CR_DZGL t
GROUP BY DJBM, DJRQ
(select SUM(YJJE) - SUM(SJJE) from CR_DZGL where DJRQ<=t.DJRQ) Expr2, Expr2- Expr1 as Expr3
FROM CR_DZGL t
GROUP BY DJBM, DJRQ, YJJE, SJJE
列名 'Expr2' 无效。
服务器: 消息 207,级别 16,状态 1,行 1
列名 'Expr1' 无效。
SELECT DJBM, DJRQ, YJJE, SJJE, SUM(YJJE) - SUM(SJJE) AS Expr1,
(select SUM(YJJE) - SUM(SJJE) from CR_DZGL where DJBM=t.DJBM and DJRQ<t.DJRQ) AS 累计未结
FROM CR_DZGL t
GROUP BY DJBM, DJRQ