目录
目录
需求
准备
月
分析
按月分组
行转列
错误版本
正确版本
日
分析
行转列
周
分析
按周分组
行转列
本年
需求
页面有三个按钮 日周月，统计一周中每天(日)，一月中每周(周)，一年中每月(月)，设备台数
点击 按钮月，出现类似下图这种
返回给前端，如果某个月份没有数据，x轴该月份不是没有了嘛，当然可以有其他方式来解决，本文主要讲下行转列
准备
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for robot
-- ----------------------------
DROP TABLE IF EXISTS `robot`;
CREATE TABLE `robot` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`createtime` datetime NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of robot
-- ----------------------------
INSERT INTO `robot` VALUES (1, '1号机器人', '2024-02-02 23:07:37');
INSERT INTO `robot` VALUES (2, '2号机器人', '2024-01-01 23:07:37');
INSERT INTO `robot` VALUES (3, '3号机器人', '2024-02-02 23:07:37');
INSERT INTO `robot` VALUES (4, '4号机器人', '2024-01-01 15:41:42');
INSERT INTO `robot` VALUES (5, '5号机器人', '2024-03-03 15:51:25');
INSERT INTO `robot` VALUES (6, '6号机器人', '2024-01-26 11:34:46');
SET FOREIGN_KEY_CHECKS = 1;
注：此处举例都是同一年，其他年份where过滤一下即可，过滤方式于文章末尾
月
分析
需要按照月去统计，那么将相同月合为一组，统计ID为数量即可，那月怎么划分？
从第6位开始后两位即为月，SUBSTRING(createtime, 6, 2)
按月分组
SELECT SUBSTRING(createtime, 6, 2) dayOfMonth, count(ID) num
FROM `robot`
GROUP BY SUBSTRING(createtime, 6, 2)
行转列
错误版本
select
case when dayOfMonth = 1 then num else 0 end January,
case when dayOfMonth = 2 then num else 0 end February,
case when dayOfMonth = 3 then num else 0 end March,
case when dayOfMonth = 4 then num else 0 end April,
case when dayOfMonth = 5 then num else 0 end May,
case when dayOfMonth = 6 then num else 0 end June,
case when dayOfMonth = 7 then num else 0 end July,
case when dayOfMonth = 8 then num else 0 end August,
case when dayOfMonth = 9 then num else 0 end September,
case when dayOfMonth = 10 then num else 0 end October,
case when dayOfMonth = 11 then num else 0 end November,
case when dayOfMonth = 12 then num else 0 end December
from (
SELECT SUBSTRING(createtime, 6, 2) dayOfMonth, count(ID) num
FROM `robot`
GROUP BY SUBSTRING(createtime, 6, 2)
) t
????????????
咋就成这样了，难不成每次 case when 同一个字段 end 不同字段 时他会将之前的结果表每行都扫描一次？那我取有值的一次是不是就解决了？怎么取？有值的那次是最大的
正确版本
select
MAX(case when dayOfMonth = 1 then num else 0 end) January,
MAX(case when dayOfMonth = 2 then num else 0 end) February,
MAX(case when dayOfMonth = 3 then num else 0 end) March,
MAX(case when dayOfMonth = 4 then num else 0 end) April,
MAX(case when dayOfMonth = 5 then num else 0 end) May,
MAX(case when dayOfMonth = 6 then num else 0 end) June,
MAX(case when dayOfMonth = 7 then num else 0 end) July,
MAX(case when dayOfMonth = 8 then num else 0 end) August,
MAX(case when dayOfMonth = 9 then num else 0 end) September,
MAX(case when dayOfMonth = 10 then num else 0 end) October,
MAX(case when dayOfMonth = 11 then num else 0 end) November,
MAX(case when dayOfMonth = 12 then num else 0 end) December
from (
SELECT SUBSTRING(createtime, 6, 2) dayOfMonth, count(ID) num
FROM `robot`
GROUP BY SUBSTRING(createtime, 6, 2)
) t
日
分析
需要一个函数帮我确定给定的日期是星期几，然后再分组统计
SELECT DAYNAME(createtime) dayOfWeek, count(ID) num
FROM `robot`
GROUP BY DAYNAME(createtime) 
行转列
select
MAX(case when dayOfWeek = 'Monday' then num else 0 end) Monday,
MAX(case when dayOfWeek = 'Tuesday' then num else 0 end) Tuesday,
MAX(case when dayOfWeek = 'Wednesday' then num else 0 end) Wednesday,
MAX(case when dayOfWeek = 'Thursday' then num else 0 end) Thursday,
MAX(case when dayOfWeek = 'Friday' then num else 0 end) Friday,
MAX(case when dayOfWeek = 'Saturday' then num else 0 end) Saturday,
MAX(case when dayOfWeek = 'Sunday' then num else 0 end) Sunday
from (
SELECT DAYNAME(createtime) dayOfWeek, count(ID) num
FROM `robot`
GROUP BY DAYNAME(createtime)
) t
周
公司要求的是：
1-7号固定为第一周，8-14号固定为第二周，15-21固定为第三周，剩下的为第四周
注：如果不是这种规则，网上找找周相关函数，很容易找到的
分析
需要按照他们定的规则划分周，那我怎么知道日期几号？
SUBSTRING(createtime, 9, 2) 9号位置开始后两位为天
按周分组
SELECT CASE WHEN SUBSTRING(createtime, 9, 2) <= 7 THEN 'firstWeek'
WHEN SUBSTRING(createtime, 9, 2) <= 14 THEN 'secondWeek'
WHEN SUBSTRING(createtime, 9, 2) <= 21 THEN 'thirdWeek'
ELSE 'fourWeek' END as `week`, count(ID) num
FROM `robot`
GROUP BY
CASE WHEN SUBSTRING(createtime, 9, 2) <= 7 THEN 'firstWeek'
WHEN SUBSTRING(createtime, 9, 2) <= 14 THEN 'secondWeek'
WHEN SUBSTRING(createtime, 9, 2) <= 21 THEN 'thirdWeek'
ELSE 'fourWeek' END
行转列
select
MAX(case when `week` = 'firstWeek' then num else 0 end) firstWeek,
MAX(case when `week` = 'secondWeek' then num else 0 end) secondWeek,
MAX(case when `week` = 'thirdWeek' then num else 0 end) thirdWeek,
MAX(case when `week` = 'fourWeek' then num else 0 end) fourWeek
from (
SELECT CASE WHEN SUBSTRING(createtime, 9, 2) <= 7 THEN 'firstWeek'
WHEN SUBSTRING(createtime, 9, 2) <= 14 THEN 'secondWeek'
WHEN SUBSTRING(createtime, 9, 2) <= 21 THEN 'thirdWeek'
ELSE 'fourWeek' END as `week`, count(ID) num
FROM `robot`
GROUP BY
CASE WHEN SUBSTRING(createtime, 9, 2) <= 7 THEN 'firstWeek'
WHEN SUBSTRING(createtime, 9, 2) <= 14 THEN 'secondWeek'
WHEN SUBSTRING(createtime, 9, 2) <= 21 THEN 'thirdWeek'
ELSE 'fourWeek' END
) t
本年
将上面的sql用下面 where 后面的过滤一下即可
SELECT *
FROM robot
WHERE YEAR(createtime) = YEAR(CURDATE()); 
