元ネタ→http://blogs.wankuma.com/episteme/archive/2008/03/22/129054.aspx
SQLLiteは手元にないのでOracle11gでやってみた。
incomeとoutgoの２つの列ではなくaccount列に１本化して符号をつけることでincomeとoutgoのどちらかが必須という制約を張らなくても良いという方法になります。
DROP TABLE cashbox;
CREATE TABLE cashbox
( key NUMBER PRIMARY KEY,
setdate DATE NOT NULL,
summary VARCHAR2(20),
account NUMBER NOT NULL,
note VARCHAR2(20)
)
/
CREATE SEQUENCE autonumber
/
CREATE OR REPLACE TRIGGER cashbox_BI
BEFORE INSERT ON cashbox FOR EACH ROW
BEGIN
-- Oracle 11g
:NEW.key := autonumber.NEXTVAL;
-- Oracle 10g
-- SELECT autonumber.NEXTVAL INTO :NEW.key FROM DUAL;
END;
/
CREATE INDEX XIE_cashbox ON cashbox ( setdate )
/
-- レコードをいくつか挿入
INSERT INTO cashbox
( setdate, summary, account )
VALUES ( '2008/02/29', 'leftover', -380);
INSERT INTO cashbox
( setdate, summary, account )
VALUES ( '2008/03/01', 'carryover', 380);
INSERT INTO cashbox
( setdate, summary, account )
VALUES ( '2008/03/01', 'from-mama', 5000);
INSERT INTO cashbox
( setdate, summary, account )
VALUES ( '2008/03/03', 'comic', -410);
INSERT INTO cashbox
( setdate, summary, account )
VALUES ( '2008/03/02', 'potato-chips', -147);
INSERT INTO cashbox
( setdate, summary, account )
VALUES ( '2008/03/04', 'from-granpa', 1000);
-- 全部出力してみる
SQL> SELECT * FROM cashbox ORDER BY setdate,key;
KEY SETDATE SUMMARY ACCOUNT NOTE
---------- -------- -------------------- ---------- --------------------
21 08-02-29 leftover -380
22 08-03-01 carryover 380
23 08-03-01 from-mama 5000
25 08-03-02 potato-chips -147
24 08-03-03 comic -410
26 08-03-04 from-granpa 1000
-- 三月の収支 (あまり良いSELECT文じゃないかも）
SQL> SELECT income.account,ABS(outgo.account),income.account+outgo.account
2 FROM
3 (SELECT sum(account) as account
4 FROM cashbox
5 WHERE TRUNC(setdate,'MM')=TO_DATE('20080301','YYYYMMDD')
6 AND account>=0) income,
7 (SELECT sum(account) as account
8 FROM cashbox
9 WHERE TRUNC(setdate,'MM')=TO_DATE('20080301','YYYYMMDD')
10 AND account<0) outgo
11 ;
ACCOUNT ABS(OUTGO.ACCOUNT) INCOME.ACCOUNT+OUTGO.ACCOUNT
---------- ------------------ ----------------------------
6380 557 5823