MySQL关联两表按交易类型行转列导出指定格式CSV问题

原有查询错误点 #

• CASE表达式未添加ELSE 0分支，交易类型不匹配时返回NULL而非要求的0值
• 多个CASE字段之间缺少逗号分隔，会触发语法报错
• 最后一个Amount-RTGS别名前多了多余空格，和要求的表头字段名不一致

修正后的查询语句 #

SELECT 
  T1.account_no,
  T1.txn_date,
  T2.Branch,
  CASE WHEN T1.transaction = 'Withdraw' THEN T1.amount ELSE 0 END AS `Amount-Withdraw`,
  CASE WHEN T1.transaction = 'NEFT' THEN T1.amount ELSE 0 END AS `Amount-NEFT`,
  CASE WHEN T1.transaction = 'IMPS' THEN T1.amount ELSE 0 END AS `Amount-IMPS`,
  CASE WHEN T1.transaction = 'RTGS' THEN T1.amount ELSE 0 END AS `Amount-RTGS`
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.account_no = T2.Account_no;

直接导出为符合要求的CSV文件 #

如果需要在MySQL中直接导出带表头的CSV，可使用以下完整语句（需确认MySQL有对应目录的写入权限，且secure_file_priv配置允许导出）：

SELECT 'Account_no','txn_date','Branch','Amount-Withdraw','Amount-NEFT','Amount-IMPS','Amount-RTGS'
UNION ALL
SELECT 
  T1.account_no,
  T1.txn_date,
  T2.Branch,
  CASE WHEN T1.transaction = 'Withdraw' THEN T1.amount ELSE 0 END AS `Amount-Withdraw`,
  CASE WHEN T1.transaction = 'NEFT' THEN T1.amount ELSE 0 END AS `Amount-NEFT`,
  CASE WHEN T1.transaction = 'IMPS' THEN T1.amount ELSE 0 END AS `Amount-IMPS`,
  CASE WHEN T1.transaction = 'RTGS' THEN T1.amount ELSE 0 END AS `Amount-RTGS`
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.account_no = T2.Account_no
INTO OUTFILE '/path/to/your/export.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

内容的提问来源于stack exchange，提问作者Sriram