作者superttl (ttl)
看板Database
标题[SQL ] mssql or postgresql 语法请教?
时间Mon Jan 23 12:48:06 2017
资料库名称：mssql or postgresql
各位资料库的前辈大家好，小弟在撰写程式时需要用到select 小计及合计及总计的
问题，再使用 google 及站内讨论有搜寻到相关的讨论但是套用在现况一直无法解出来，
以下是小弟的测试资料（以 postgresql为例）。
create table test_tbl(
bank_code varchar(7),
con_amt1 int8,
con_amt2 int8,
con_amt3 int8,
con_type char(1),
col_type char(2)
);
nsert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0040037',500,0,300,'H','00');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0040037',300,0,300,'H','00');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0040037',700,0,0,'H','21');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0041241',300,0,100,'H','21');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0041241',300,0,150,'H','14');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0041241',100,0,100,'F','14');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0041241',100,0,50,'F','14');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0041241',50,0,20,'L','14');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0041241',50,0,30,'L','14');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0050016',400,0,300,'H','00');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0050016',400,0,300,'H','00');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0050016',300,0,0,'H','21');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0050016',200,0,100,'H','21');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0050027',200,0,150,'H','14');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0050027',150,0,100,'F','14');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0050027',120,0,50,'F','14');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0050027',60,0,20,'L','14');
insert into test_tbl(bank_code,con_amt1,con_amt2,con_amt3,con_type,col_type)
values('0050027',60,0,30,'L','14');
资料输出如下：
select bank_code, con_type,col_type,con_amt1,con_amt2,con_amt3 from test_tbl;
bank_code | con_type | col_type | con_amt1 | con_amt2 | con_amt3
-----------+----------+----------+----------+----------+----------
0040037 | H | 00 | 500 | 0 | 300
0040037 | H | 00 | 300 | 0 | 300
0040037 | H | 21 | 700 | 0 | 0
0041241 | H | 21 | 300 | 0 | 100
0041241 | H | 14 | 300 | 0 | 150
0041241 | F | 14 | 100 | 0 | 100
0041241 | F | 14 | 100 | 0 | 50
0041241 | L | 14 | 50 | 0 | 20
0041241 | L | 14 | 50 | 0 | 30
0050016 | H | 00 | 400 | 0 | 300
0050016 | H | 00 | 400 | 0 | 300
0050016 | H | 21 | 300 | 0 | 0
0050016 | H | 21 | 200 | 0 | 100
0050027 | H | 14 | 200 | 0 | 150
0050027 | F | 14 | 150 | 0 | 100
0050027 | F | 14 | 120 | 0 | 50
0050027 | L | 14 | 60 | 0 | 20
0050027 | L | 14 | 60 | 0 | 30
(18 rows)
想要的结果如下：
1.依bank_code前三码+con_type+col_type 小计
2.依bank_code前三码+con_type 合计
3.依bank_code前三码 总计
4.依所有bank_code 之最後总计
结果图片:
http://imgur.com/a/5lXW3
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 123.192.251.152
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Database/M.1485146888.A.0E8.html