MySQL按周统计 WEEK 实例
时间：2021-07-01 10:21:17
帮助过：20人阅读
16) DEFAULT NULL,
`in_weight` varchar(64) DEFAULT NULL,
`out_weight` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=
utf8;
--
插入样例数据
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2018-03-10‘,
‘18.84‘,
‘74.14‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-01-21‘,
‘17.69‘,
‘25.97‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-02-01‘,
‘16.48‘,
‘10.0‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-01-29‘,
‘10.9‘,
‘74.12‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-02-13‘,
‘15.25‘,
‘16.62‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-01-12‘,
‘19.21‘,
‘95.42‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-02-14‘,
‘26.38‘,
‘20.59‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-01-24‘,
‘15.99‘,
‘16.823‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-01-13‘,
‘94.846‘,
‘56.256‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-03-03‘,
‘15.63‘,
‘12.128‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-03-12‘,
‘14.282‘,
‘24.046‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-01-16‘,
‘14.045‘,
‘11.73‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-03-08‘,
‘23.41‘,
‘23.441‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-01-31‘,
‘11.177‘,
‘4332.26‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-01-23‘,
‘11.8‘,
‘17104.49‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-03-17‘,
‘17.95‘,
‘13.81‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-03-01‘,
‘19.23‘,
‘20.43‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-02-05‘,
‘21.55‘,
‘51.0‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-02-27‘,
‘13.003‘,
‘23.32‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-03-11‘,
‘16.44‘,
‘24.44‘);
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES (‘2019-02-28‘,
‘16.48‘,
‘23.4‘);
-- 创建结果表，插入统计结果
drop TABLE if exists week_count_tb;
CREATE TABLE `week_count_tb` (
`week_day` varchar(32) DEFAULT NULL,
`week_num` varchar(32) DEFAULT NULL,
`in_weight` varchar(64) DEFAULT NULL,
`out_weight` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
delete from week_count_tb;
INSERT INTO week_count_tb
SELECT
*
FROM
(
SELECT
middle.week_day,
middle.week_num,
sum(in_weight) in_weight,
sum(out_weight) out_weight
FROM
(
SELECT
subdate(
date_sub(day_date, INTERVAL 1 DAY),
date_format(date_sub(day_date, INTERVAL 1 DAY),‘%w‘) - 7) week_day,
WEEK (
date_add(subdate(
date_sub(day_date, INTERVAL 1 DAY),
date_format(date_sub(day_date, INTERVAL 1 DAY),‘%w‘) - 7),INTERVAL 6 DAY),2) week_num,
in_weight,
out_weight
FROM
day_weight_tb
) middle
GROUP BY
week_day,
week_num
) result;
思路：先确定一天是属于一年中的具体周数，再获取到一周中的周末日期，最后根据周数分组求和。
MySQL按周统计 WEEK 实例
标签：creat create 间隔 form 统计 set 查看 获取 out
