[狼头]VB版有600分的技术帖

Question

http://topic.csdn.net/u/20101211/11/b4119ee5-a216-479c-bc98-b0d38d0e9c52.html?3866
去回答，大家周末快乐。

Answer 1

楼主你的例子显然示经验证来测试。
mysql> select * from DepositDetail;
+-----------+------------+-------------+----------+---------------+-----------+--------------------+-------------+---------+---------+-------------+--------+----------------+
| DepositID | CustomerID | DepositType | Amount   | Description   | CashierID |TransDate           | TransStatus | TransID | OrderID | PaymentType | TermID | DepositGroupID |
+-----------+------------+-------------+----------+---------------+-----------+--------------------+-------------+---------+---------+-------------+--------+----------------+
|         1 |          1 | Cash        | 200.0000 | INWARD CR-IBG |         1 |2010-10-07 11:21:14 |           1 |       0 |       0 |             |      1 |              1 |
|         2 |          1 | Cash        | 300.0000 | INWARD CR-IBG |         1 |2010-10-07 11:28:40 |           1 |       0 |       0 |             |      1 |              1 |
|         3 |          1 | Cash        | 600.0000 | Paid          |         1 |2010-10-07 11:48:44 |           2 |       0 |       0 |             |      1 |              1 |
|         4 |          2 | Cash        | 800.0000 | INWARD CR-IBG |         1 |2010-10-07 12:00:28 |           1 |       0 |       0 |             |      1 |              1 |
|         5 |          2 | Cash        |  60.0000 | Deposit       |         1 |2010-10-07 15:33:24 |           1 |       0 |       0 |             |      1 |              1 |
|         6 |          2 | Cash        |  80.0000 | Deposit       |         1 |2010-11-07 15:52:24 |           1 |       0 |       0 |             |      1 |              1 |
|         7 |          2 | Cash        | 500.0000 | Deposit       |         1 |2010-11-17 16:09:11 |           2 |       0 |       0 |             |      1 |              1 |
|         8 |          1 | Cash        | 100.0000 | Deposit       |         1 |2010-11-27 16:11:34 |           2 |       0 |       0 |             |      1 |              1 |
|         9 |          2 | Cash        | 200.0000 | Deposit       |         1 |2010-12-27 16:14:04 |           1 |       0 |       0 |             |      1 |              1 |
|        10 |          1 | Cash        |  23.0000 | Deposit       |         1 |2010-10-07 16:38:11 |           2 |       0 |       0 |             |      1 |              1 |
|        11 |          1 | Cash        | 250.0000 | Deposit       |         1 |2010-12-07 16:57:52 |           1 |       0 |       0 |             |      1 |              1 |
|        12 |          3 | Cash        |  50.0000 | Deposit       |         1 |2010-09-07 17:01:18 |           2 |       0 |       0 |             |      1 |              1 |
|        13 |          3 | VISA        |  60.0000 | Deposit       |         1 |2010-12-07 17:10:03 |           1 |       0 |       0 |             |      1 |              1 |
+-----------+------------+-------------+----------+---------------+-----------+--------------------+-------------+---------+---------+-------------+--------+----------------+
13 rows in set (0.05 sec)mysql> select TransDate,CustomerID,Description,
    ->  if(TransStatus=1,Amount,0) as `in`,
    ->  if(TransStatus=2,Amount,0) as `out`,
    ->  (select sum(if(TransStatus=1,Amount,-Amount)) from DepositDetail where CustomerID=t.CustomerID and TransDate<=t.sk) as Balance
    -> from(
    -> select date(TransDate) as TransDate,CustomerID,Description,TransStatus,Amount,TransDate as sk
    -> from DepositDetail
    -> where CustomerID=1
    -> union
    -> select date(TransDate)-interval day(TransDate) day + interval 1 day,CustomerID,'OPENBALANCE' as Description,
    ->  1,0,date(TransDate)-interval day(TransDate) day + interval 1 day
    -> from DepositDetail
    -> where CustomerID=1
    -> ) t
    -> order by sk;
+------------+------------+---------------+----------+----------+-----------+
| TransDate  | CustomerID | Description   | in       | out      | Balance   |
+------------+------------+---------------+----------+----------+-----------+
| 2010-10-01 |          1 | OPENBALANCE   |   0.0000 |        0 |      NULL |
| 2010-10-07 |          1 | INWARD CR-IBG | 200.0000 |        0 |  200.0000 |
| 2010-10-07 |          1 | INWARD CR-IBG | 300.0000 |        0 |  500.0000 |
| 2010-10-07 |          1 | Paid          |        0 | 600.0000 | -100.0000 |
| 2010-10-07 |          1 | Deposit       |        0 |  23.0000 | -123.0000 |
| 2010-11-01 |          1 | OPENBALANCE   |   0.0000 |        0 | -123.0000 |
| 2010-11-27 |          1 | Deposit       |        0 | 100.0000 | -223.0000 |
| 2010-12-01 |          1 | OPENBALANCE   |   0.0000 |        0 | -223.0000 |
| 2010-12-07 |          1 | Deposit       | 250.0000 |        0 |   27.0000 |
+------------+------------+---------------+----------+----------+-----------+
9 rows in set (0.03 sec)mysql>而你期望的结果是
很明显你是浪费别人的时间！