表结构:DROP TABLE IF EXISTS `refunds_tb`;
CREATE TABLE `refunds_tb` (
`Id` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '主键',
`ContractId` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '合同id',
`RefundsAmount` int(11) DEFAULT NULL COMMENT '回款金额',
`RefundsDate` datetime DEFAULT NULL COMMENT '回款日期'
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
SET FOREIGN_KEY_CHECKS=1;表数据:
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '2000', '2017-03-17 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '4000', '2017-03-18 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '2000', '2017-03-17 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('d76a41b2192640d4942bab455ffd8655', '8800', '2017-03-14 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '12200', '2017-03-15 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '2000', '2017-03-17 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1000', '2017-03-09 10:39:55');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '2000', '2017-03-14 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('d76a41b2192640d4942bab455ffd8655', '8800', '2017-03-14 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2f32f7e0c04a4f37ae2f37133820462a', '52000', '2017-03-09 10:39:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '2000', '2017-03-17 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '4000', '2017-03-20 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('d76a41b2192640d4942bab455ffd8655', '2000', '2017-03-14 15:19:03');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('d76a41b2192640d4942bab455ffd8655', '2400', '2017-03-14 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('d76a41b2192640d4942bab455ffd8655', '6800', '2017-03-14 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '1000', '2017-03-13 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '1600', '2017-03-13 09:03:28');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '4000', '2017-03-18 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
统计某个月份以前的sql可以这样写:select sum(rt.RefundsAmount) RefundsAmount,rt.ContractId from refunds_tb rt where and DATE_FORMAT(rt.RefundsDate,'%Y-%m')<='2017-03' GROUP BY ContractId
如果要统计一年所有月份的怎么弄?不用union all方式
CREATE TABLE `refunds_tb` (
`Id` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '主键',
`ContractId` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '合同id',
`RefundsAmount` int(11) DEFAULT NULL COMMENT '回款金额',
`RefundsDate` datetime DEFAULT NULL COMMENT '回款日期'
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
SET FOREIGN_KEY_CHECKS=1;表数据:
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '2000', '2017-03-17 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '4000', '2017-03-18 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '2000', '2017-03-17 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('d76a41b2192640d4942bab455ffd8655', '8800', '2017-03-14 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '12200', '2017-03-15 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '2000', '2017-03-17 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1000', '2017-03-09 10:39:55');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '2000', '2017-03-14 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('d76a41b2192640d4942bab455ffd8655', '8800', '2017-03-14 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2f32f7e0c04a4f37ae2f37133820462a', '52000', '2017-03-09 10:39:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '2000', '2017-03-17 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '4000', '2017-03-20 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('d76a41b2192640d4942bab455ffd8655', '2000', '2017-03-14 15:19:03');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('d76a41b2192640d4942bab455ffd8655', '2400', '2017-03-14 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('d76a41b2192640d4942bab455ffd8655', '6800', '2017-03-14 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '1000', '2017-03-13 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '1600', '2017-03-13 09:03:28');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('2e904eddeadd457781e0b8ea7aa6f2d6', '4000', '2017-03-18 00:00:00');
INSERT INTO ``.`` (`ContractId`, `RefundsAmount`, `RefundsDate`) VALUES ('6d24f88eee31450fa90fd388f65f58e4', '1400', '2017-03-08 00:00:00');
统计某个月份以前的sql可以这样写:select sum(rt.RefundsAmount) RefundsAmount,rt.ContractId from refunds_tb rt where and DATE_FORMAT(rt.RefundsDate,'%Y-%m')<='2017-03' GROUP BY ContractId
如果要统计一年所有月份的怎么弄?不用union all方式
----
是不是每个月一条数据?
from
(select DATE_FORMAT(rt.RefundsDate,'%Y-%m') as RefundsDate,rt.ContractId,sum(rt.RefundsAmount) RefundsAmount, from refunds_tb rt where and DATE_FORMAT(rt.RefundsDate,'%Y-%m')<='2017-03' GROUP BY DATE_FORMAT(rt.RefundsDate,'%Y-%m') as RefundsDate,ContractId) a
SELECT SUM(RefundsAmount),CONCAT(YEAR(RefundsDate),'-',MONTH(RefundsDate)) MONTH FROM refunds_tb GROUP BY CONCAT(YEAR(RefundsDate),MONTH(RefundsDate));