myql如何达成行转列统计查询
发布时间：2022-01-12 10:54:00 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍了myql如何实现行转列统计查询，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 1 原始数据 -- ---------------------------- -- Table structure for `t_bm_repeat_pur
|
这篇文章主要介绍了myql如何实现行转列统计查询，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 1 原始数据 -- ---------------------------- -- Table structure for `t_bm_repeat_purchase` -- ---------------------------- DROP TABLE IF EXISTS `t_bm_repeat_purchase`; CREATE TABLE `t_bm_repeat_purchase` ( `months` int(2) DEFAULT NULL COMMENT '月份', `total` bigint(21) NOT NULL DEFAULT '0' COMMENT '查询月份对应的下一个月后或几个月后的购买用户数', `seq` bigint(20) DEFAULT NULL COMMENT '序列号', `next_months` bigint(4) DEFAULT NULL COMMENT 'months 字段对应的第几个月后，1月后，2月后，3月后。。。' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of t_bm_repeat_purchase -- ---------------------------- INSERT INTO `t_bm_repeat_purchase` VALUES ('1', '1170', '2', '2'); INSERT INTO `t_bm_repeat_purchase` VALUES ('1', '2144', '2', '3'); INSERT INTO `t_bm_repeat_purchase` VALUES ('1', '1012', '2', '4'); INSERT INTO `t_bm_repeat_purchase` VALUES ('1', '873', '2', '5'); INSERT INTO `t_bm_repeat_purchase` VALUES ('1', '785', '2', '6'); INSERT INTO `t_bm_repeat_purchase` VALUES ('1', '1008', '2', '7'); INSERT INTO `t_bm_repeat_purchase` VALUES ('1', '773', '2', '8'); INSERT INTO `t_bm_repeat_purchase` VALUES ('2', '1446', '2', '3'); INSERT INTO `t_bm_repeat_purchase` VALUES ('2', '700', '2', '4'); INSERT INTO `t_bm_repeat_purchase` VALUES ('2', '665', '2', '5'); INSERT INTO `t_bm_repeat_purchase` VALUES ('2', '533', '2', '6'); INSERT INTO `t_bm_repeat_purchase` VALUES ('2', '694', '2', '7'); INSERT INTO `t_bm_repeat_purchase` VALUES ('2', '551', '2', '8'); INSERT INTO `t_bm_repeat_purchase` VALUES ('3', '1530', '2', '4'); INSERT INTO `t_bm_repeat_purchase` VALUES ('3', '1273', '2', '5'); INSERT INTO `t_bm_repeat_purchase` VALUES ('3', '1062', '2', '6'); INSERT INTO `t_bm_repeat_purchase` VALUES ('3', '1367', '2', '7'); INSERT INTO `t_bm_repeat_purchase` VALUES ('3', '1044', '2', '8'); INSERT INTO `t_bm_repeat_purchase` VALUES ('4', '1035', '2', '5'); INSERT INTO `t_bm_repeat_purchase` VALUES ('4', '775', '2', '6'); INSERT INTO `t_bm_repeat_purchase` VALUES ('4', '949', '2', '7'); INSERT INTO `t_bm_repeat_purchase` VALUES ('4', '790', '2', '8'); INSERT INTO `t_bm_repeat_purchase` VALUES ('5', '939', '2', '6'); INSERT INTO `t_bm_repeat_purchase` VALUES ('5', '1304', '2', '7'); INSERT INTO `t_bm_repeat_purchase` VALUES ('5', '1066', '2', '8'); INSERT INTO `t_bm_repeat_purchase` VALUES ('6', '1110', '2', '7'); INSERT INTO `t_bm_repeat_purchase` VALUES ('6', '899', '2', '8'); INSERT INTO `t_bm_repeat_purchase` VALUES ('7', '1589', '2', '8'); 2 用动态查询 ： SET @EE=''; set @str_tmp=''; SELECT @EE:=CONCAT(@EE,'SUM(IF(next_months='',next_months,''',',total,null)) AS "',next_months,'",') as aa into @str_tmp FROM (SELECT DISTINCT next_months FROM t_bm_repeat_purchase) A order by length(aa) desc limit 1; SET @QQ=CONCAT('SELECT t_bm_repeat_purchase.months,',left(@str_tmp,char_length(@str_tmp)-1),' FROM t_bm_repeat_purchase GROUP BY months '); PREPARE stmt FROM @QQ; EXECUTE stmt ; deallocate prepare stmt; 动态查询结果：这不是最终我们想要的，舍弃这种查询方法，因为前面为空的数据，还要将后面的数据整体向左平移 3 用静态查询 SELECT t.months, IF(0>num,NULL,SUBSTRING_INDEX(total, ',', 1)) AS '1', IF(1>num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 2),',',-1)) AS '2', -- 这个是算取第1个数 IF(2>num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 3) ,',',-1)) AS '3', -- 取第2个数 IF(3>num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 4) ,',',-1)) AS '4', -- 取第三个数 IF(4>num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 5) ,',',-1)) AS '5', IF(5>num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 6) ,',',-1)) AS '6', IF(6>num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 7) ,',',-1)) AS '7', IF(7>num,NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 8) ,',',-1)) AS '8', IF(8>num,NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 9) ,',',-1)) AS '9', IF(9>num,NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 10) ,',',-1)) AS '10', IF(10>num,NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 11) ,',',-1)) AS '11' FROM (SELECT a.months, CHAR_LENGTH(GROUP_CONCAT(a.total))-CHAR_LENGTH(replace(GROUP_CONCAT(a.total),',','')) as num, -- 这个是算每个月有几个逗号 GROUP_CONCAT(a.total ORDER BY a.next_months) AS total FROM t_bm_repeat_purchase a GROUP BY a.months ) t; SELECT a.months, CHAR_LENGTH(GROUP_CONCAT(a.total))-CHAR_LENGTH(replace(GROUP_CONCAT(a.total),',','')) as num, -- 这个是算每个月有几个逗号 GROUP_CONCAT(a.total ORDER BY a.next_months) AS total FROM t_bm_repeat_purchase a 对其进行优化 SELECT t.months, IF(num>=1,SUBSTRING_INDEX(total, ',', 1),NULL) AS '1', IF(num>=2,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 2),',',-1) ,NULL) AS '2', IF(num>=3,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 3) ,',',-1),NULL) AS '3', IF(num>=4,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 4) ,',',-1),NULL) AS '4', IF(num>=5,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 5) ,',',-1),NULL) AS '5', IF(num>=6,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 6) ,',',-1),NULL) AS '6', IF(num>=7,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 7) ,',',-1),NULL) AS '7', IF(num>=8,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 8) ,',',-1),NULL) AS '8', IF(num>=9,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 9) ,',',-1),NULL) AS '9', IF(num>=10,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 10) ,',',-1),NULL) AS '10', IF(num>=11,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 11) ,',',-1),NULL) AS '11' FROM (SELECT a.months, COUNT(*) as num, -- 这边取每个月分别有多少个数据 GROUP_CONCAT(a.total ORDER BY a.next_months) AS total FROM t_bm_repeat_purchase a GROUP BY a.months) t; 4 动态查询和静态查询优缺点 动态的话，我目前没能做到达到最终结果，并且不方便做insert 表 ，但是可以不限多少月，也就是适用于无限数据的 静态的话 对于基数不大的话，比如12个月，6个月这种能较快列举完的比较合适，对于基数大的就不方便，但是方便做insert 表 ，并且静态的我现在可以做到 需求的要求，所以目前采用动态的做法 感谢你能够认真阅读完这篇文章，希望小编分享的“myql如何实现行转列统计查询”这篇文章对大家有帮助 （编辑：好传媒网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
