本次散分帖SQL语句-400分

lxq19851204 2010-12-11 11:02:31



CREATE TABLE `DepositDetail` (

  `DepositID` INTEGER  NOT NULL AUTO_INCREMENT,

  `CustomerID` INTEGER NOT NULL DEFAULT 0,

  `DepositType` varchar(16) ,

  `Amount` DECIMAL(19,4),

  `Description` VARCHAR(120),

  `CashierID` INTEGER,

  `TransDate` Datetime,

  `TransStatus` INTEGER ,

  `TransID` INTEGER DEFAULT 0,

  `OrdreID` INTEGER DEFAULT 0,

  `PaymentType` VARCHAR(10),

  `TermID` INTEGER,

  `DepositGroupID` INTEGER,

  PRIMARY KEY (`DepositID`),

  INDEX `CustomerID`(`CustomerID`),

  index `TransDate`(`TransDate`)

)ENGINE = InnoDB DEFAULT CHARSET=utf8;

insert into depositdetail(DepositID,CustomerID,DepositType,Amount,Description,CashierID,TransDate,TransStatus,TransID,OrderID,PaymentType,TermID,DepositGroupID) value

(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)



当TransStatus=1时，属于进帐，=2时，属于出帐。

统计顾客每个月的进出帐报告。每个月的第一天算出上一个月的帐，以OpenBalance

例如：(拿CUSTOMERID=1来举例)

TransDate   CustomerID   Description       in     out   Balance

01/10/2010    1           OPENBALANCE       0        0     0

07/10/2010    1           INWARD CR-IBG    200      0     200

07/10/2010    1           INWARD CR-IBG    300      0     500

07/10/2010    1           Paid             0       600     -100

07/10/2010    1           INWARD CR-IBG    23       0      -78

01/11/2010    1           OPENBALANCE      0        0      -78

07/10/2010    1           Paid            100       0      -178

01/12/2010    1           OPENBALANCE      0        0      -178

07/10/2010    1           DEPOSIT          0        250    72

....

...全文

85 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

WWWWA 2010-12-11

打赏
举报

select a.CustomerID,sum(if(DepositType=1,Amount,-1*Amount)) from depositdetail a
left join depositdetail b
on a.CustomerID=b.CustomerID and a.TransDate>=b.TransDate
group by a.CustomerID

lxq19851204 2010-12-11

打赏
举报

是问题，只是我分给的多。600

iihero 2010-12-11

打赏
举报

是问题，还是接分？ seat and wait....

無_1024 2010-12-11

打赏
举报

原来还是沙发啊

無_1024 2010-12-11

打赏
举报

哦那我来接分了啊哈哈

lxq19851204 2010-12-11

打赏
举报

[Quote=引用 7 楼 acmain_chm 的回复:]
楼主你的例子显然示经验证来测试。

SQL code
mysql> select * from DepositDetail;
+-----------+------------+-------------+----------+---------------+-----------+--------------------+-------------+---------+---------……
[/Quote]
呵呵，刚才看错了。数据是我临时添加的，老板还在我旁边走来走去，所以有点错误。

蓝海 2010-12-11

打赏
举报

谢谢，楼主老大：多多给分哦！

lxq19851204 2010-12-11

打赏
举报

(10, 1, 'Cash', 23.0000, 'Deposit', 1, '2010-10-07 16:38:11', 2, 0, 0, '', 1, 1)
这条纪录是属于出账的，

ACMAIN_CHM 2010-12-11

打赏
举报

楼主你的例子显然示经验证来测试。

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>

而你期望的结果是
[Quote]当TransStatus=1时，属于进帐，=2时，属于出帐。
统计顾客每个月的进出帐报告。每个月的第一天算出上一个月的帐，以OpenBalance
例如：(拿CUSTOMERID=1来举例)
TransDate CustomerID Description in out Balance
01/10/2010 1 OPENBALANCE 0 0 0
07/10/2010 1 INWARD CR-IBG 200 0 200
07/10/2010 1 INWARD CR-IBG 300 0 500
07/10/2010 1 Paid 0 600 -100
07/10/2010 1 INWARD CR-IBG 23 0 -78
01/11/2010 1 OPENBALANCE 0 0 -78
07/10/2010 1 Paid 100 0 -178
01/12/2010 1 OPENBALANCE 0 0 -178
07/10/2010 1 DEPOSIT 0 250 72[/Quote]

很明显你是浪费别人的时间！

ACMAIN_CHM 2010-12-11

打赏
举报

楼主有没有测试过你自己提供的数据？！

另外如下红色记录是怎么来的？

TransDate CustomerID Description in out Balance
01/10/2010 1 OPENBALANCE 0 0 007/10/2010 1 INWARD CR-IBG 200 0 200
07/10/2010 1 INWARD CR-IBG 300 0 500
07/10/2010 1 Paid 0 600 -100
07/10/2010 1 INWARD CR-IBG 23 0 -7801/11/2010 1 OPENBALANCE 0 0 -7807/10/2010 1 Paid 100 0 -17801/12/2010 1 OPENBALANCE 0 0 -178
07/10/2010 1 DEPOSIT 0 250 72

本次实习体会最深刻的就是需求分析，通过写需求分析文档，我深刻的体会到了需求分析在整个系统设计中的重要性即：要全面的进行需求分析，了解用户的需求，这样可以减少以后设计的困难，完成需求分析对整个系统的功能...

分有些是线程私有的，有些则是线程共享的线程私有的：程序计数器，虚拟机栈，本地方法栈线程共享的：方法区，堆 2.JVM 中是怎么判断对象可回收的？可达性分析算法这个算法的基本思想就是通过一系列的称为 “GC ...

Math的round方法是四舍五入,如果参数是负数,则往大的数如,Math.round(-1.5)=-1 String 属于基础的数据类型吗？不是,String是一个类,是引用数据类型. java 中操作字符串都有哪些类？它们之间有...

–java中四舍五入的原理是在现有参数上加0.5,.那么就是-1 6. String 属于基础的数据类型吗？不是,是最基本的引用类型,Java中的基本类型有8个,byte,short,int ,long,float,double ,boolean,char; 7. java 中操作...

load返回的是代理对象(javassist.jar生成二进制码)，等到真正用到对象的内容才会发出SQL语句 get直接从数据库加载，不会延迟 get不支持懒加载，load支持 get查询数据库不存在的记录时返回null ，load就报...