myql如何實現行轉列統計查詢

這篇文章主要介紹了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
   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 表 ，并且靜態的我現在可以做到  需求的要求，所以目前采用動態的做法

感謝你能夠認真閱讀完這篇文章，希望小編分享的“myql如何實現行轉列統計查詢”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!