这是我的疑问...
+-----+----+----+------------+------------+-----+----------+-------+----------+
| seq | i | WN | fld_Date | next_date | CBU | balance | ndays | tbal |
+-----+----+----+------------+------------+-----+----------+-------+----------+
| 67 | 1 | 46 | 2012-11-13 | 2012-11-20 | 50 | 50.00 | 7 | 350.00 |
| 68 | 2 | 47 | 2012-11-20 | 2012-11-27 | 50 | 100.00 | 7 | 700.00 |
| 69 | 3 | 48 | 2012-11-27 | 2012-12-04 | 50 | 150.00 | 7 | 1,050.00 |
| 70 | 4 | 49 | 2012-12-04 | 2012-12-11 | 50 | 200.00 | 7 | 1,400.00 |
| 71 | 5 | 50 | 2012-12-11 | 2012-12-18 | 50 | 250.00 | 7 | 1,750.00 |
| 72 | 6 | 51 | 2012-12-18 | 2012-12-25 | 50 | 300.00 | 7 | 2,100.00 |
| 73 | 7 | 52 | 2012-12-25 | 2013-01-01 | 50 | 350.00 | 7 | 2,450.00 |
| 74 | 8 | 1 | 2013-01-01 | 2013-01-08 | 50 | 400.00 | 7 | 2,800.00 |
| 75 | 9 | 2 | 2013-01-08 | 2013-01-15 | 50 | 450.00 | 7 | 3,150.00 |
| 76 | 10 | 3 | 2013-01-15 | 2013-01-22 | 50 | 500.00 | 7 | 3,500.00 |
| 77 | 11 | 4 | 2013-01-22 | 2013-01-29 | 50 | 550.00 | 7 | 3,850.00 |
| 78 | 12 | 5 | 2013-01-29 | 2013-02-05 | 50 | 600.00 | 7 | 4,200.00 |
| 79 | 13 | 6 | 2013-02-05 | 2013-02-12 | 50 | 650.00 | 7 | 4,550.00 |
| 80 | 14 | 7 | 2013-02-12 | 2013-02-19 | 50 | 700.00 | 7 | 4,900.00 |
| 81 | 15 | 8 | 2013-02-19 | 2013-02-26 | 50 | 750.00 | 7 | 5,250.00 |
| 82 | 16 | 9 | 2013-02-26 | 2013-03-05 | 50 | 800.00 | 7 | 5,600.00 |
| 83 | 17 | 10 | 2013-03-05 | 2013-03-12 | 50 | 850.00 | 7 | 5,950.00 |
| 84 | 18 | 11 | 2013-03-12 | 2013-03-19 | 50 | 900.00 | 7 | 6,300.00 |
| 85 | 19 | 12 | 2013-03-19 | 2013-03-26 | 50 | 950.00 | 7 | 6,650.00 |
| 86 | 20 | 13 | 2013-03-26 | 2013-04-02 | 50 | 1,000.00 | 7 | 7,000.00 |
| 87 | 21 | 14 | 2013-04-02 | 2013-04-09 | 50 | 1,050.00 | 7 | 7,350.00 |
| 88 | 22 | 15 | 2013-04-09 | 0 | 50 | 1,100.00 | 0 | 0.00 |
+-----+----+----+------------+------------+-----+----------+-------+----------+
所有我想要的是总结tbal,ndays和平衡,但我得到了一个问题,在tbal当我使用SUM函数...这必须是输出,我试图得到...请帮助我这个谢谢...
cbu = 1,100.00
balance = 12,650.00
ndays = 147
tbal = 80,850.00
这是sql...
SELECTseq,@i := @i + 1 i,WEEKOFYEAR(fld_Date) AS WN,fld_Date,next_date,CBU,FORMAT(COALESCE (@balance := @balance + CBU, 0),2) AS balance,(@ndays := COALESCE (DATEDIFF(next_date, fld_Date),0)) ndays,FORMAT(@tbal := COALESCE (@Balance *@ndays, 0),2) AS tbal FROM (SELECT db_lms.lms_savings.seq,@d next_date,@d := db_lms.lms_savings.fld_Date fld_Date, db_lms.lms_savings.CBU FROM db_lms.lms_savings,(SELECT @d := 0) d,(SELECT @balance := 0) e ORDER BY db_lms.lms_savings.seq DESC) q,(SELECT @i := 0) n ORDER BY seq;
1条答案
按热度按时间cdmah0mi1#
已更新
输出:
下面是SQLFiddle演示