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) $
我的系列文档
| Netkiller Architect 手札 | Netkiller Developer 手札 | Netkiller php 手札 | Netkiller Python 手札 | Netkiller TesTing 手札 | Netkiller Cryptography 手札 |
| Netkiller Linux 手札 | Netkiller CentOS 手札 | Netkiller FreeBSD 手札 | Netkiller Security 手札 | Netkiller Version 手札 | Netkiller Web 手札 |
| Netkiller Monitoring 手札 | Netkiller Storage 手札 | Netkiller Mail 手札 | Netkiller SHell 手札 | Netkiller Network 手札 | Netkiller Database 手札 |
| Netkiller POSTGReSQL 手札 | Netkiller MySQL 手札 | Netkiller NoSQL 手札 | Netkiller LDAP 手札 | Netkiller Cisco IOS 手札 | Netkiller H3C 手札 |
| Netkiller Multimedia 手札 | Netkiller Docbook 手札 | Netkiller 开源软件 手札 |
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,-- 余额 datetiR_766_11845@e 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 | datetiR_766_11845@e | 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)