作者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/m.aspx?n=bbs/Database/M.1485146888.A.0E8.html