多个列相加问题，用mysql如何实现？

Question

 CREATE TABLE `am_budget_input` ( 		
   `ID` int(10) NOT NULL COMMENT '单据号', 		
   `BA_ID` int(10) DEFAULT NULL COMMENT '科目ID', 		
   `AP_ID` int(10) DEFAULT NULL COMMENT '辅助账ID', 		
   `INPUT_PERID` int(10) NOT NULL COMMENT '登记人艾玛', 		
   `INPUT_PERN` varchar(20) NOT NULL COMMENT '登记人名称', 		
   `INPUT_DATE` datetime NOT NULL COMMENT '登记时间', 		
   `AUDIT_PERID` int(10) DEFAULT NULL COMMENT '审核人代码', 		
   `AUDIT_PERN` varchar(20) DEFAULT NULL COMMENT '审核人名称', 		
   `AUDIT_DATE` datetime DEFAULT NULL COMMENT '审核时间', 		
   `STATUS` int(5) DEFAULT NULL COMMENT '状态', 		
   `CHANNEL_ID` int(10) DEFAULT NULL COMMENT '门店ID', 		
   `ACCOUNT_ID` int(10) DEFAULT NULL COMMENT '账套ID', 		
   `YEAR` int(5) DEFAULT NULL COMMENT '年份', 		
   `TOTAL_AMOUNT` decimal(14,2) DEFAULT NULL COMMENT '年度预算总额', 		
   PRIMARY KEY (`ID`) 		
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 		

 CREATE TABLE `am_budget_input_dtl` ( 		
   `ID` int(10) NOT NULL COMMENT '单据号', 		
   `TYPE_ID` int(10) NOT NULL COMMENT '类型ID', 		
   `MONTH01` decimal(14,2) DEFAULT NULL COMMENT '1月预算', 		
   `MONTH02` decimal(14,2) DEFAULT NULL COMMENT '2月预算', 		
   `MONTH03` decimal(14,2) DEFAULT NULL COMMENT '3月预算', 		
   `MONTH04` decimal(14,2) DEFAULT NULL COMMENT '4月预算', 		
   `MONTH05` decimal(14,2) DEFAULT NULL COMMENT '5月预算', 		
   `MONTH06` decimal(14,2) DEFAULT NULL COMMENT '6月预算', 		
   `MONTH07` decimal(14,2) DEFAULT NULL COMMENT '7月预算', 		
   `MONTH08` decimal(14,2) DEFAULT NULL COMMENT '8月预算', 		
   `MONTH09` decimal(14,2) DEFAULT NULL COMMENT '9月预算', 		
   `MONTH10` decimal(14,2) DEFAULT NULL COMMENT '10月预算', 		
   `MONTH11` decimal(14,2) DEFAULT NULL COMMENT '11月预算', 		
   `MONTH12` decimal(14,2) DEFAULT NULL COMMENT '12月预算', 		
   PRIMARY KEY (`ID`,`TYPE_ID`) 		
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='预算录入明细'; 		

两张表，一张主表，一张子表。两张表通过id关联。现在需求是这样，给定某个时间段，比如2020-05-2021-03，我需要怎样得到这之间的预算列之和？后端用的是springboot+mybatis。需要在service里拼sql字符串吗？还是在dao层可以解决？

Answer 1

我向了好久，觉得创建一个视图，转花为方便查询的结构，这样可以很容易按年（JION主表）和月日渐段查询和汇总了。

CREATE VIEW am_budget_monthly_input AS
SELECT ID, TYPE_ID, 01 AS MONTH, MONTH01 AS BUDGET FROM am_budget_input_dtl UNION ALL
SELECT ID, TYPE_ID, 02 AS MONTH, MONTH02 AS BUDGET FROM am_budget_input_dtl UNION ALL
SELECT ID, TYPE_ID, 03 AS MONTH, MONTH03 AS BUDGET FROM am_budget_input_dtl UNION ALL
SELECT ID, TYPE_ID, 04 AS MONTH, MONTH04 AS BUDGET FROM am_budget_input_dtl UNION ALL
SELECT ID, TYPE_ID, 05 AS MONTH, MONTH05 AS BUDGET FROM am_budget_input_dtl UNION ALL
SELECT ID, TYPE_ID, 06 AS MONTH, MONTH06 AS BUDGET FROM am_budget_input_dtl UNION ALL
SELECT ID, TYPE_ID, 07 AS MONTH, MONTH07 AS BUDGET FROM am_budget_input_dtl UNION ALL
SELECT ID, TYPE_ID, 08 AS MONTH, MONTH08 AS BUDGET FROM am_budget_input_dtl UNION ALL
SELECT ID, TYPE_ID, 09 AS MONTH, MONTH09 AS BUDGET FROM am_budget_input_dtl UNION ALL
SELECT ID, TYPE_ID, 10 AS MONTH, MONTH10 AS BUDGET FROM am_budget_input_dtl UNION ALL
SELECT ID, TYPE_ID, 11 AS MONTH, MONTH11 AS BUDGET FROM am_budget_input_dtl UNION ALL
SELECT ID, TYPE_ID, 11 AS MONTH, MONTH11 AS BUDGET FROM am_budget_input_dtl
;