我的Database Design..

一只优雅的猪

Date       ID           Type    Debit         Credit         Outstanding         Balance
1-10-06    DN-1111       DN     86.00           0.00               86.00           86.00   
4-10-06    CN-8888       PAY     0.00          56.00               30.00           30.00
1-11-06    DN-2222       DN    120.00           0.00              150.00          150.00
1-12-06    DN-3333       DN     15.00           0.00              165.00          165.00  
5-12-06    CN-9999       PAY     0.00         166.00                0.00            0.00

我需要使用sql statement来得到以上的query result.. 但发觉所设计的database design有缺陷，行不通，请大家赐教！

以下是我目前使用的database (MYSQL 4.1)

DEBIT_NOTE_TABLE
DN_ID     TYPE   AMOUNT    DATE       MEM_ID
DN-1111    DN       86.00     2006-10-01   JACK
DN-2222    DN      120.00     2006-11-01   JACK
DN-3333    DN       15.00     2006-12-01   JACK

CREDIT_NOTE_TABLE
CN_ID     TYPE    AMOUNT    DATE         MEM_ID
CN-8888    PAY        56.00     2006-10-04     JACK
CN-9999    PAY       166.00     2006-12-05     JACK

[ 本帖最后由 一只优雅的猪 于 23-1-2007 05:43 PM 编辑 ]

一只优雅的猪

CREATE TABLE `credit_note_table` (
  `CN_ID` varchar(45) NOT NULL default '',
  `type` varchar(45) NOT NULL default '',
  `amount` varchar(45) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `mem_id` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`CN_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `credit_note_table` (`CN_ID`,`type`,`amount`,`date`,`mem_id`) VALUES
('CN-8888','PAY','56.00','2006-10-04 00:00:00','JACK'),
('CN-9999','PAY','166.00','2006-12-05 00:00:00','JACK');

CREATE TABLE `debit_note_table` (
  `DN_ID` varchar(45) NOT NULL default '',
  `type` varchar(45) NOT NULL default '',
  `amount` varchar(45) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `mem_id` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`DN_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `debit_note_table` (`DN_ID`,`type`,`amount`,`date`,`mem_id`) VALUES
('DN-1111','DN','86.00','2006-10-01 00:00:00','JACK'),
('DN-2222','DN','120.00','2006-11-01 00:00:00','JACK'),
('DN-3333','DN','15.00','2006-12-01 00:00:00','JACK');

一只优雅的猪

以下是我编写的query.. 解决部分问题，但我不知道如何编写 "outstanding" & "balance" 这两个fields （我使用mysql 4.1)

(SELECT date, dn_id AS ID, Type, amount AS Debit, 0.00 AS Credit
FROM debit_note_table)
UNION
(SELECT date, cn_id AS ID, Type, 0.00 AS Debit, amount AS Credit
FROM credit_note_table)

[ 本帖最后由 一只优雅的猪 于 24-1-2007 10:46 AM 编辑 ]

goatstudio

问题很含糊不清呢.

outstanding 和 balance 的条件是什么呢?

一只优雅的猪

Date       ID       Type     Debit     Credit    Outstanding    Balance
1-10-06    DN-1111  DN       86.00       0.00          86.00      86.00    (record1)
4-10-06    CN-8888  PAY       0.00      56.00          30.00      30.00    (record2)
1-11-06    DN-2222  DN      120.00       0.00         150.00     150.00
1-12-06    DN-3333  DN       15.00       0.00         165.00     165.00  
5-12-06    CN-9999  PAY       0.00     165.00           0.00       0.00

if Type = DN then
record2.outstanding = record2.debit + record1.balance
record2.balance = record2.debit + record1.balance

elseif Type = PAY then
record2.outstanding = record1.balance - record2.credit
record2.balance     = record1.balance - record2.credit

[ 本帖最后由 一只优雅的猪 于 24-1-2007 11:39 AM 编辑 ]

goatstudio

啊老兄...

怎么你的解释常常都是一半的...

DN 是什么? Pay 是什么?

outstanding 是那一个条件? balance 又是那一个条件?

一只优雅的猪

不好意思。。

-----------------------------------------------------------------------------
Date       ID       Type     Debit     Credit    Balance
1-10-06    DN-1111  DN       86.00       0.00       86.00   
4-10-06   CN-8888  PAY       0.00      56.00       30.00   
1-11-06    DN-2222  DN      120.00       0.00      150.00
1-12-06    DN-3333  DN       15.00       0.00      165.00  
5-12-06    CN-9999  PAY       0.00     165.00        0.00
-----------------------------------------------------------------------------

DN and PAY 都是under Type 这个category 的values

获取balance的条件：
我拿 4-10-06 这个record做解析吧...

如果 4-10-06的 type = PAY, 那么
4-10-06 的 balance = 1-10-06 的 balance MINUS 4-10-06的credit

----------------------------------

如果 4-10-06的 type = DN, 那么
4-10-06 的 balance = 1-10-06 的 balance ADD 4-10-06的debit

p/s: 我不需要得到outstanding这个value了

[ 本帖最后由 一只优雅的猪 于 24-1-2007 12:36 PM 编辑 ]

goatstudio

原帖由 一只优雅的猪 于 24-1-2007 12:27 PM 发表

-----------------------------------------------------------------------------
Date       ID       Type     Debit     Credit    Balance
1-10-06    DN-1111  DN       86.00       0.00       86.00   
4-10-06   CN-8888  PAY       0.00      56.00       30.00   
1-11-06    DN-2222  DN      120.00       0.00      150.00
1-12-06    DN-3333  DN       15.00       0.00      165.00  
5-12-06    CN-9999  PAY       0.00     165.00        0.00
-----------------------------------------------------------------------------

这资料是从一个 table 来的, 还是  credit_note_table 和 debit_note_table?
如果是从 credit_note_table 和 debit_note_table, 那么你的 sql 怎么写?

一只优雅的猪

goatstudio:

是from 两个table而来的。。。 目前我写的sql只是不能显示balance这个value而已
---------------------------------------------------------------------------------

SELECT i.date, i.ID, i.Type, i.debit, i.credit FROM
(
SELECT dn.date, dn.dn_id AS ID, dn.Type, dn.amount AS Debit, 0.00 AS Credit FROM debit_note_table dn
UNION ALL
SELECT cn.date, cn.cn_id AS ID, cn.Type, 0.00 AS Debit, cn.amount AS Credit FROM credit_note_table cn
) i ORDER BY date ASC


使用以上的sql可以得到以下的query result..
-------------------------------------------------------
Date       ID       Type     Debit     Credit   
1-10-06    DN-1111  DN       86.00       0.00      
4-10-06   CN-8888  PAY       0.00      56.00      
1-11-06    DN-2222  DN      120.00       0.00      
1-12-06    DN-3333  DN       15.00       0.00      
5-12-06    CN-9999  PAY       0.00     165.00   

------------------------------------------------------------------

你可以使用以下的sql statement来得知我的database structure & data

CREATE TABLE `credit_note_table` (
  `CN_ID` varchar(45) NOT NULL default '',
  `type` varchar(45) NOT NULL default '',
  `amount` varchar(45) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `mem_id` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`CN_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `credit_note_table` (`CN_ID`,`type`,`amount`,`date`,`mem_id`) VALUES
('CN-8888','PAY','56.00','2006-10-04 00:00:00','JACK'),
('CN-9999','PAY','166.00','2006-12-05 00:00:00','JACK');


CREATE TABLE `debit_note_table` (
  `DN_ID` varchar(45) NOT NULL default '',
  `type` varchar(45) NOT NULL default '',
  `amount` varchar(45) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `mem_id` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`DN_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `debit_note_table` (`DN_ID`,`type`,`amount`,`date`,`mem_id`) VALUES
('DN-1111','DN','86.00','2006-10-01 00:00:00','JACK'),
('DN-2222','DN','120.00','2006-11-01 00:00:00','JACK'),
('DN-3333','DN','15.00','2006-12-01 00:00:00','JACK');
     

goatstudio

这个似乎很难用一个 sql 来完成, 因为你的 balance 直接影响每一行的资料. 例如说, 第一行的 balance 影响到下一个月一号的 balance, 所以建议你还是用 temp table 的方法来达到.

一只优雅的猪

问题1) 你的意思是说sql写不到读取上一条record的资料，借此获取想得到的value?
问题2）所谓temp table, 是指什么呢？如何运用在我的需求呢？

以下是一个朋友给我的“解决方法”，
他说是可行地，但他使用的是Oracle syntax, 我转换红色highlight那两行时出现错误


select date, id , type, debit, credit,
sum(debit + credit * -1) over (order by date) outstanding,
sum(debit + credit * -1) over (order by date) balance
from
(
select date, id, type, case when type = 'DN' then amount else 0 end debit,
case when type = 'PAY' then amount else 0 end credit
from (
select date, dn_id id , 'DN' type, amount from debit_note_table
union all
select date, cn_id id, 'PAY' type, amount from credit_note_table )
)

一只优雅的猪

以下是我的trial sql.. 还是 不行。。。 请问如何apply temp_table来获取我想的得到的query result 呢？

---------------------------------------------------------------------------------------------

SELECT i.date, i.ID, i.Type, i.debit, i.credit,

(SELECT SUM(i2.credit - i2.balance) FROM
SELECT dn2.amount AS Debit, 0.00 AS Credit FROM debit_note_table dn2 WHERE dn2.date < i.date
UNION ALL
SELECT 0.00 AS Debit, cn2.amount AS Credit FROM credit_note_table cn2 WHERE cn2.date < i.date
) i2
)AS balance FROM

(
SELECT dn.date, dn.dn_id AS ID, dn.Type, dn.amount AS Debit, 0.00 AS Credit FROM debit_note_table dn
UNION ALL
SELECT cn.date, cn.cn_id AS ID, cn.Type, 0.00 AS Debit, cn.amount AS Credit FROM credit_note_table cn
) i
GROUP BY i.date, i.ID, i.Type
ORDER BY date ASC;

goatstudio

暂时还没了解到 MySQL 的 While 要怎么 loop thru 整个 recordset, MSSQL 倒可以轻易达到.

如果我是你, 我会建立一个 dummy table, 把你 union 回来的 record 全部注入这 dummy 里, 再用 coding 如 php 还是什么的一行一行去 update.

悠哉亭

sum(xxx) over (order by yyyy)
是 Oracle 支援命令,不一定其它数据库专也支援.

你的SQL问题应该出于 select clause 里边的inner query (3rd level) join with from clause 的 query
"dn2.date < i.date"

哎呀不懂解释..

你可以用view解决这问题.

CREATE VIEW temp_view AS
SELECT dn.DATE, dn.dn_id AS ID, dn.TYPE, dn.amount AS Debit, 0.00 AS Credit FROM debit_note_table dn
UNION ALL
SELECT cn.DATE, cn.cn_id AS ID, cn.TYPE, 0.00 AS Debit, cn.amount AS Credit FROM credit_note_table cn


SELECT i.DATE, i.ID, i.TYPE, i.debit, i.credit,
i.debit -i.credit
+(SELECT SUM(v.debit ) FROM temp_view v WHERE v.DATE < i.DATE )
-(SELECT SUM(v.credit ) FROM temp_view v WHERE v.DATE < i.DATE)  AS balance
FROM temp_view i
ORDER BY DATE ASC;

不过当数据库越大时, performance 可能会慢哦.


不然的话,采取goatstudio大大的建议.

[ 本帖最后由 悠哉亭 于 25-1-2007 05:41 PM 编辑 ]