手机网站怎样做,app编辑软件,网站发布与推广,如何用浏览器访问本地的wordpress目录
目录
需求
准备
月
分析
按月分组
行转列
错误版本
正确版本
日
分析
行转列
周
分析
按周分组
行转列
本年 需求
页面有三个按钮 日周月#xff0c;统计一周中每天(日)#xff0c;一月中每周(周)#xff0c;一年中每月(月)#xff0c;设备台数
点…目录
目录
需求
准备
月
分析
按月分组
行转列
错误版本
正确版本
日
分析
行转列
周
分析
按周分组
行转列
本年 需求
页面有三个按钮 日周月统计一周中每天(日)一月中每周(周)一年中每月(月)设备台数
点击 按钮月出现类似下图这种 返回给前端如果某个月份没有数据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) numFROM robotGROUP 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) numFROM robotGROUP 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 firstWeekWHEN SUBSTRING(createtime, 9, 2) 14 THEN secondWeekWHEN SUBSTRING(createtime, 9, 2) 21 THEN thirdWeekELSE fourWeek END as week, count(ID) numFROM robot GROUP BY CASE WHEN SUBSTRING(createtime, 9, 2) 7 THEN firstWeekWHEN SUBSTRING(createtime, 9, 2) 14 THEN secondWeekWHEN SUBSTRING(createtime, 9, 2) 21 THEN thirdWeekELSE 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 firstWeekWHEN SUBSTRING(createtime, 9, 2) 14 THEN secondWeekWHEN SUBSTRING(createtime, 9, 2) 21 THEN thirdWeekELSE fourWeek END as week, count(ID) numFROM robot GROUP BY CASE WHEN SUBSTRING(createtime, 9, 2) 7 THEN firstWeekWHEN SUBSTRING(createtime, 9, 2) 14 THEN secondWeekWHEN SUBSTRING(createtime, 9, 2) 21 THEN thirdWeekELSE fourWeek END
) t 本年
将上面的sql用下面 where 后面的过滤一下即可
SELECT *
FROM robot
WHERE YEAR(createtime) YEAR(CURDATE());
