CREATE TABLE `tbl_direct_pos_201506` ( `acq_ins_code` char(13) NOT NULL DEFAULT '' COMMENT '機構代碼', `trace_num` char(6) NOT NULL DEFAULT '' COMMENT '跟蹤號', `trans_datetime` char(10) NOT NULL DEFAULT '' COMMENT '交易時間', `process_flag` char(1) DEFAULT NULL COMMENT '處理標識', `rev_flag` char(1) DEFAULT NULL COMMENT '接收標識', `before_trans_code` char(3) DEFAULT NULL COMMENT '交易類型', `trans_amt` decimal(15,3) DEFAULT NULL COMMENT '交易金額', `acct_num` char(21) DEFAULT NULL COMMENT '卡號', `mer_type` char(4) DEFAULT NULL COMMENT '商戶類型', `recv_ins_code` char(13) DEFAULT NULL COMMENT '發卡行代碼', `retrivl_ref_num` char(12) DEFAULT NULL COMMENT '檢索參考號', `resp_auth_code` char(6) DEFAULT NULL COMMENT '授權碼', `resp_code` char(2) DEFAULT NULL COMMENT '應答碼', `term_id` char(8) DEFAULT NULL COMMENT '終端代碼', `mer_code` char(15) DEFAULT NULL COMMENT '商戶代碼', `mer_addr_name` char(40) DEFAULT NULL COMMENT '商戶名稱和地址,前 25 字節是名稱,後面是地址', `self_define` varchar(300) DEFAULT NULL COMMENT '第 259 字節是卡片類型', `sys_date` char(8) NOT NULL DEFAULT '' COMMENT '交易日期', `sa_sav2` varchar(300) DEFAULT NULL COMMENT '第 243 字節是 DCC 標識', `rec_create_time` datetime DEFAULT NULL COMMENT '聯機入庫時間', `rec_update_time` datetime DEFAULT NULL COMMENT '最後修改時間', PRIMARY KEY (`sys_date`,`trans_datetime`,`acq_ins_code`,`trace_num`), KEY `idx_direct_pos_create_time` (`rec_create_time`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='交易月表模板';
select substr(t.acq_ins_code, 3) merAcqInsCode, t.mer_code, t.term_id, substr(t.mer_addr_name, 1, 12) merName, tt.trans_desc, t.rev_flag, t.trans_amt, concat(substr(t.sys_date, 1, 4), t.trans_datetime) transTime,t.before_trans_code, t.acct_num, t.retrivl_ref_num, t.resp_code, t.resp_auth_code, r.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_datetime, case substr(t.sa_sav2,259,1) when 1 then '借記卡' when 2 then '貸記卡' when 3 then '准貸記卡' when 4 then '私有預付卡' else '' end cardType, case when locate('VIS',t.sa_sav2) > 0 then 'VISA' when locate('JCB',t.sa_sav2) > 0 then 'JCB' when locate('DNC',t.sa_sav2) > 0 then '大萊卡' when locate('CUP',t.sa_sav2) > 0 then '銀聯境內卡' when locate('UPI',t.sa_sav2) > 0 then '銀聯境外卡' else '' end cardBrand from tbl_direct_pos_201506 t left join trans_recv_ins r on r.recv_ins_code = t.recv_ins_code left join tbl_trans_type tt on tt.trans_code = t.before_trans_code where t.sys_date between '20150622' and '20150628' order by t.sys_date, t.trans_datetime, t.acq_ins_code, t.trace_num limit 0, 20;
select substr(t.acq_ins_code, 3) merAcqInsCode, t.mer_code, t.term_id, substr(t.mer_addr_name, 1, 12) merName, tt.trans_desc, t.rev_flag, t.trans_amt, concat(substr(t.sys_date, 1, 4), t.trans_datetime) transTime,t.before_trans_code, t.acct_num, t.retrivl_ref_num, t.resp_code, t.resp_auth_code, r.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_datetime, case substr(t.sa_sav2,259,1) when 1 then '借記卡' when 2 then '貸記卡' when 3 then '准貸記卡' when 4 then '私有預付卡' else '' end cardType, case when locate('VIS',t.sa_sav2) > 0 then 'VISA' when locate('JCB',t.sa_sav2) > 0 then 'JCB' when locate('DNC',t.sa_sav2) > 0 then '大萊卡' when locate('CUP',t.sa_sav2) > 0 then '銀聯境內卡' when locate('UPI',t.sa_sav2) > 0 then '銀聯境外卡' else '' end cardBrand from tbl_direct_pos_201506 t left join trans_recv_ins r on r.recv_ins_code = t.recv_ins_code left join tbl_trans_type tt on tt.trans_code = t.before_trans_code where t.sys_date between '20150622' and '20150628' order by t.sys_date desc, t.trans_datetime desc, t.acq_ins_code desc, t.trace_num desc limit 0, 20;
執行之,0.029 s,搞定。