共计 4905 个字符，预计需要花费 13 分钟才能阅读完成。
这篇文章主要介绍了 myql 如何实现行转列统计查询，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。
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
GROUP BY a.months 这个语句下查询结果：
对其进行优化
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 表，并且静态的我现在可以做到 需求的要求，所以目前采用动态的做法
感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“myql 如何实现行转列统计查询”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!
