[SQL ] mssql or postgresql 語法請教?

看板Database (資料庫)作者superttl (ttl)時間7年前 (2017/01/23 12:48)推噓0(0推 0噓 0→)

留言0則, 0人參與討論串1/1

資料庫名稱：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://www.ptt.cc/bbs/Database/M.1485146888.A.0E8.html