这样的sql如何写？

[jobslew2603765]

我有个表要做如下的统计。
DATE NO JE XFJE LJJE
----------------------------------------------------------
2006-05-04 123 100 100
2006-05-05 123 55 10 140
2006-05-06 123 45 10 175

我求的sql 就是如何统计这个LJJE，他的公式是
例如：（2006-05-06的LJJE）=（2006-05-06的JE）-（2006-05-06的XFJE）+（2006-05-05的LJJE）

[OracleRoob]

--楼主给的结果不对吧！

CREATE TABLE #T(DATE DATETIME, NO INT, JE INT, XFJE INT, LJJE INT)

INSERT INTO #T(DATE,NO,JE,XFJE)
SELECT '2006-05-04', 123, 100, NULL UNION ALL
SELECT '2006-05-05', 123, 55,10 UNION ALL
SELECT '2006-05-06', 123, 45, 10

SELECT * FROM #T

SELECT [DATE],[NO],JE,XFJE,
ISNULL(JE,0)-ISNULL(XFJE,0)+CASE WHEN (SELECT SUM(ISNULL(JE,0)-ISNULL(XFJE,0)) FROM #T WHERE [DATE]<A.[DATE]) IS NULL THEN 0 ELSE (SELECT SUM(ISNULL(JE,0)-ISNULL(XFJE,0)) FROM #T WHERE [DATE]<A.[DATE]) END AS LJJE
FROM #T A

DROP TABLE #T

/*
--执行结果

DATE NO JE XFJE LJJE
----------------------------------------------------------
2006-05-04 123 100 100
2006-05-05 123 55 10 145
2006-05-06 123 45 10 180
*/

[jobslew2603765]

在单个的NO 的是时候是对的，但是你看有两个NO的时候，
INSERT INTO T(DATE,NO,LXJE,ZSWPJE)
SELECT '2006-05-07', 123, 100, NULL
UNION ALL
SELECT '2006-05-05', 123, 55,10
UNION ALL
SELECT '2006-05-06', 123, 45, 10
UNION ALL
SELECT '2006-05-07', 124, 100, NULL
UNION ALL
SELECT '2006-05-05', 124, 55,10
UNION ALL
SELECT '2006-05-06', 124, 45, 10

SELECT * FROM T a
--SELECT * FROM T a
--where a.date='2006-05-07'

SELECT [DATE],[NO],LXJE,ZSWPJE,
ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)+
CASE WHEN (SELECT SUM(ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)) FROM T WHERE [DATE]<A.[DATE]) IS NULL THEN 0
ELSE (SELECT SUM(ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)) FROM T WHERE [DATE]<A.[DATE]) END AS LJJE
FROM T A
where a.date='2006-05-05'

SELECT [DATE],[NO],LXJE,ZSWPJE,
ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)+
CASE WHEN (SELECT SUM(ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)) FROM T WHERE [DATE]<A.[DATE]) IS NULL THEN 0
ELSE (SELECT SUM(ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)) FROM T WHERE [DATE]<A.[DATE]) END AS LJJE
FROM T A
where a.date='2006-05-06'


SELECT [DATE],[NO],LXJE,ZSWPJE,
ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)+
CASE WHEN (SELECT SUM(ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)) FROM T WHERE [DATE]<A.[DATE]) IS NULL THEN 0
ELSE (SELECT SUM(ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)) FROM T WHERE [DATE]<A.[DATE]) END AS LJJE
FROM T A
where a.date='2006-05-07'


结果如下：
DATE NO LXJE ZSWPJE LJJE
------------------------------------------------------ ------------ --------------------- --------------------- ---------------------
2006-05-05 00:00:00 123 55.0000 10.0000 45.0000
2006-05-05 00:00:00 124 55.0000 10.0000 45.0000

（所影响的行数为 2 行）

DATE NO LXJE ZSWPJE LJJE
------------------------------------------------------ ------------ --------------------- --------------------- ---------------------
2006-05-06 00:00:00 123 45.0000 10.0000 125.0000
2006-05-06 00:00:00 124 45.0000 10.0000 125.0000

（所影响的行数为 2 行）

DATE NO LXJE ZSWPJE LJJE
------------------------------------------------------ ------------ --------------------- --------------------- ---------------------
2006-05-07 00:00:00 123 100.0000 NULL 260.0000
2006-05-07 00:00:00 124 100.0000 NULL 260.0000

（所影响的行数为 2 行）

[paoluo]

改動下，另外那個CASE改用ISNULL來處理.


--建立測試環境
Create Table T(DATE Varchar(10),NO Int,LXJE Int,ZSWPJE Int)
INSERT INTO T(DATE,NO,LXJE,ZSWPJE)
SELECT '2006-05-07', 123, 100, NULL
UNION ALL
SELECT '2006-05-05', 123, 55,10
UNION ALL
SELECT '2006-05-06', 123, 45, 10
UNION ALL
SELECT '2006-05-07', 124, 100, NULL
UNION ALL
SELECT '2006-05-05', 124, 55,10
UNION ALL
SELECT '2006-05-06', 124, 45, 10
--測試
SELECT
[DATE],
[NO],
LXJE,ZSWPJE,
ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)+
ISNULL((SELECT SUM(ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)) FROM T WHERE [DATE]<A.[DATE] And [NO]=A.[NO]),0)AS LJJE
FROM T A
Order By [DATE],[NO]
--刪除測試環境
Drop Table T
--結果
/*
DATE NO LXJE ZSWPJE LJJE
2006-05-05 123 55 10 45
2006-05-05 124 55 10 45
2006-05-06 123 45 10 80
2006-05-06 124 45 10 80
2006-05-07 123 100 NULL 180
2006-05-07 124 100 NULL 180
*/

[jobslew2603765]

SELECT * FROM T
DATE NO LXJE ZSWPJE LJJE
2006-05-05 123 55 10 0
2006-05-05 124 55 10 0
2006-05-06 123 45 10 0
2006-05-06 124 45 10 0
2006-05-07 123 100 NULL 0
2006-05-07 124 100 NULL 0

LXJE 是为0的
有什么简单的方法把
SELECT
ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)+
ISNULL((SELECT SUM(ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)) FROM T WHERE [DATE]<A.[DATE] And [NO]=A.[NO]),0)AS LJJE
FROM T A
Order By [DATE],[NO]
查询的数值，

写入到LXJE 这个列里面呢？

[paoluo]

try


Update A
Set LJJE=ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)+ISNULL((SELECT SUM(ISNULL(LXJE,0)-ISNULL(ZSWPJE,0)) FROM T WHERE [DATE]<A.[DATE] And [NO]=A.[NO]),0)
from T A