会计报表的SQL

Question

在此先感谢您的帮助。

我的问题与 MySQL 中的两个表有关(现在切换到 postgresql)。这些表与票务数据库相关。

a) booking. It has four columns       ccode,date,time,amountb) account  It has three columns      ccode,date,amount

预订表有机票预订，账户表有预付款和已收到的付款。

我必须准备一份基于ccode(客户代码)的账户报表。

语句显示列如下

*Ccode    Type    Date     time    amount    balance*- the report in sorted on ccode and then on date (account table row appears first)- Type column displays B or A depending on record type- Time column is present only in booking table- Report has a running balance for each row - At the end for a customercode, the totals of amount and balance is displayed

到目前为止，我已经成功地创建了一个连接，如下所示。 (经过下面的讨论，已经能够使用 IF 生成 TYPE 列)

   SELECT     booking.cname, booking.bdate, booking.btime, booking.rate, booking.ID,           IF(booking.btime IS NOT NULL, "B", "A") AS type, account.cname, account.date,           account.amount, account.ID FROM       booking LEFT JOIN  account ON booking.bdate = account.date AND booking.cname=account.cname AND            booking.rate = account.amount  UNION SELECT     booking.cname, booking.bdate, booking.btime, booking.rate, booking.ID,            IF(booking.btime IS NOT NULL, "B", "A") AS type, account.cname, account.date,           account.amount, account.ID  FROM       booking RIGHT JOIN account ON booking.bdate = account.date AND booking.cname=account.cname AND            booking.rate = account.amount

显示所有记录。可以使用此表生成报告。

但是有没有办法只通过SQL来显示格式化的报告。<我>我可以更改列的顺序，甚至可以添加或删除现有的列，只要已知记录类型并且针对每条记录显示运行余额即可。

样本报告----报告A

CODE          DATE           TYPE              AMOUNT            BALANCE    TIMEA1           02/19/2011       A                 50               50        A1           02/20/2011       B                 35               15          1230A1           02/21/2011       A                 40               55A1           02/21/2011       B                 20               35          1830optional > TOTAL Account = 90    Booking = 55    Balance = 35 

样本报告----报告B

CODE          AMOUNT BOOKED             AMOUNT PAID           BALANCEA1                      50               50                     0A1                      35               15                     20A1                      40               55                    -15A1                      20               35                    -15this is a weekly statement version of REPORT A. the reason is i can add where and between to get only records in a given week. and since it is a weekly report, running balance is just omitted. It is report grouped for all entries with customercode A1 present    in booking and account tables.thanx

Answer 1

由于您的数据未为此进行规范化，因此您付出了查询复杂性的代价，如下所示:

SELECT    ccode, date, time, type, amount, 
          (SELECT SUM(amount) AS balance 
          FROM numbered AS n 
          WHERE numbered.rownum <= n.rownum AND numbered.ccode = n.ccode) AS balance 
FROM      (SELECT sorted.*, @rownum := @rownum + 1 AS rownum 
          FROM      (SELECT * 
                    FROM      (SELECT ccode, date, time, 'B' AS type, amount 
                              FROM booking
                              UNION
                              SELECT ccode, date, "0000" AS time, 'A' AS type, -amount 
                              FROM account) AS unsorted
                    ORDER BY ccode, date, time, type) AS sorted) AS numbered

这里的想法是，您首先需要将您的预订(借记)和帐户(贷记)排列起来，如上面的“未分类”声明中所示。然后，您需要按照“已排序”语句中的日期和时间对它们进行排序。接下来，将行号添加到结果中，如“编号”语句中所示。最后，选择所有数据以及行号小于或等于与您的 ccode 匹配的当前行号的金额总和。

将来，请考虑使用某种交易表，将所有账户余额变化保存在一个表中。