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层可以解决？
我向了好久，觉得创建一个视图，转花为方便查询的结构，这样可以很容易按年（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
;
时间段是动态给定的。
表结构设计太差。为什么不设计成一个月份字段和一个预算字段呢？
可以在service里获取到时间段内的所有预算在手动瓶装