Netkiller Postgresql 手札
文档始创于2012-11-16
版权 © 2010,2011,2012,2013 Netkiller(Neo Chan). All rights reserved.
版权声明
转载请与作者联系，转载时请务必标明文章原始出处和作者信息及本声明。
|
|
|
$Date: 2013-04-10 15:03:49 +0800 (Wed,10 Apr 2013) $
我的系列文档
Postgresql 实用实例参考
PostgreSQL 实用实例参考 (PDF)2004-07-20
PostgreSQL 实用实例参考 (BIG5 PDF)2004-07-20
PostgreSQL 实用实例参考 (HTML)2004-07-20
PostgreSQL 实用实例参考 (BIG5 HTML)2004-07-20
5.3.账户表/余额表/消费储蓄表
此表适用于购物车等金钱来往账面等等。
-- Table: account
-- DROP TABLE account;
CREATE TABLE account
(
id integer NOT NULL DEFAULT nextval('trade_id_seq'::regclass),no character varying(10) NOT NULL,-- 账号
balance money NOT NULL DEFAULT 0.00,-- 余额
datetime timestamp without time zone NOT NULL DEFAULT (now())::timestamp(0) without time zone,CONSTRAINT account_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE account
OWNER TO dba;
COMMENT ON COLUMN account.no IS '账号';
COMMENT ON COLUMN account.balance IS '余额';
-- Index: account_no_idx
-- DROP INDEX account_no_idx;
CREATE INDEX account_no_idx
ON account
USING btree
(no COLLATE pg_catalog."default");
账户结余计算
select acc.*,(select sum(balance)+acc.balance from account as ac where ac.id < acc.id) as profit from account as acc; test=# select acc.*,(select sum(balance)+acc.balance from account as ac where ac.id < acc.id) as profit from account as acc; id | no | balance | datetime | profit ----+------+----------+---------------------+--------- 1 | 1000 | $0.00 | 2013-10-09 10:51:10 | 2 | 1000 | $12.60 | 2013-10-09 10:51:22 | $12.60 4 | 1000 | $16.80 | 2013-10-09 10:51:42 | $29.40 5 | 1000 | $100.00 | 2013-10-09 10:51:49 | $129.40 6 | 1000 | $200.00 | 2013-10-09 10:56:35 | $329.40 7 | 1000 | $50.45 | 2013-10-09 10:57:23 | $379.85 8 | 1000 | $75.50 | 2013-10-09 10:57:31 | $455.35 9 | 1000 | -$55.30 | 2013-10-09 10:59:28 | $400.05 10 | 1000 | -$200.00 | 2013-10-09 10:59:44 | $200.05 (9 rows)
