编写SQL查询实现年月数据映射及借贷金额转换

样本数据表 #

id company yr   Jan      Feb          Mar      Apr
1   1      17   0.00     0.00         98.80    10.10
2   1      18   -143.60  90.60        -102.90  20.20
3   1      22   0.00     -10.20       -60.80   -30.30
4   2      23   50.90    -100.00       4.64    -40.40

转换规则 #

• 若月份列的值为正数，存入CREDITAMOUNT字段；若为负数，去掉负号后存入DEBITAMOUNT字段
• 将月份与年份拼接为MONTH列（格式示例：Jan2017，即月份缩写+完整年份，如yr为17则对应2017）

预期查询结果 #

CREDITAMOUNT   DEBITAMOUNT   MONTH
0.00           0.00          Jan2017
0.00           143.60        Jan2018
0.00           0.00          Jan2022
50.90          0.00          Jan2023
0.00           0.00          Feb2017
90.60          0.00          Feb2018
0.00           10.20         Feb2022 
0.00           100.00        Feb2023
98.80          0.00          Mar2017
0.00           102.90        Mar2018
0.00           60.80         Mar2022 
4.64           0.00          Mar2023
10.10          0.00          Apr2017
20.20          0.00          Apr2018
0.00           30.30         Apr2022 
0.00           40.40         Apr2023

实现SQL查询语句 #

以下是适用于多数关系型数据库的查询语句，通过UNION ALL完成行转列，再按规则处理金额字段：

SELECT
    CASE WHEN val > 0 THEN val ELSE 0.00 END AS CREDITAMOUNT,
    CASE WHEN val < 0 THEN ABS(val) ELSE 0.00 END AS DEBITAMOUNT,
    CONCAT(month_name, '20', yr) AS MONTH
FROM (
    SELECT Jan AS val, 'Jan' AS month_name, yr FROM your_table
    UNION ALL
    SELECT Feb AS val, 'Feb' AS month_name, yr FROM your_table
    UNION ALL
    SELECT Mar AS val, 'Mar' AS month_name, yr FROM your_table
    UNION ALL
    SELECT Apr AS val, 'Apr' AS month_name, yr FROM your_table
) AS unpivoted
ORDER BY MONTH;

注：将your_table替换为实际表名；若年份存储为完整格式，调整CONCAT的拼接逻辑即可。

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