mysql中如何实现行到列的统计查询

1 原始数据
--------------------------------
-- `t_bm_repeat_purchase` 的表结构
----------------------------
如果存在则删除表`t_bm_repeat_purchase `;
创建表 `t_bm_repeat_purchase` (
`months` int(2) DEFAULT NULL COMMENT 'month',
`total` bigint(21) NOT NULL DEFAULT '0' COMMENT '查询该月对应的下个月或几个月后的购买用户数',
`seq` bigint(20) DEFAULT NULL COMMENT '序列号',
`next_months` bigint(4) DEFAULT NULL COMMENT 'months 字段对应的月份，一月之后，二月之后，三月之后...'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -- --------------------------
-- Rt_bm_repeat_purchase 的记录
-- ---------- ---------------------
INSERT INTO `t_bm_repeat_purchase` VALUES ('1 ', '1170', '2', '2');
插入`t_bm_repeat_purchase`值('1','2144','2','3');
插入` t_bm_repeat_purchase` 值 ('1', '1012', '2', '4');
插入 `t_bm_repeat_purchase` 值 ('1', '873', '2', '5');< br/>插入`t_bm_repeat_purchase`值('1','785','2','6');
插入`t_bm_repeat_purchase`值('1','1008','2', '7');
插入`t_bm_repeat_purchase`值('1','773','2','8');
插入`t_bm_repeat_purchase`值('2','1446) ', '2', '3');
插入`t_bm_repeat_purchase`值('2','700','2','4');
插入`t_bm_repeat_purchase`值( '2', '665', '2', '5');
插入 `t_bm_repeat_purchase` 值 ('2', '533', '2', '6');
插入`t_bm_repeat_purchase`值('2','694','2','7');
插入`t_bm_repeat_purchase`值('2','551','2') , '8');
插入我NTO `t_bm_repeat_purchase` 值 ('3', '1530', '2', '4');
插入 `t_bm_repeat_purchase` 值 ('3', '1273', '2', '5') ;
插入`t_bm_repeat_purchase`值('3','1062','2','6');
插入`t_bm_repeat_purchase`值('3','1367','2 ', '7');
插入 `t_bm_repeat_purchase` 值 ('3', '1044', '2', '8');
插入 `t_bm_repeat_purchase` 值 ('4', '1035', '2', '5 ');
插入`t_bm_repeat_purchase`值('4','775','2','6');
插入`t_bm_repeat_purchase`值 ('4', '949', '2', '7');
插入 `t_bm_repeat_purchase` 值 ('4', '790', '2', '8');
插入`t_bm_repeat_purchase`值('5','939','2','6');
插入`t_bm_repeat_purchase`值('5','1304','2','7 ');
插入 `t_bm_repeat_purchase` 值 ('5', '1066', '2', '8');
插入`t_bm_repeat_purchase`值('6','1110','2','7');
插入`t_bm_repeat_purchase`值('6','899','2') , '8');
插入`t_bm_repeat_purchase` 值 ('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,'" ,') 作为 aa 到 @str_tmp
FROM (SELECT DISTINCT next_months FROM t_bm_repeat_purchase) 按 length(aa) desc limit 1 排序；
SET @QQ=CONCAT('SELECT t_bm_repeat_purchase.months,',left(@str_tmp,char_length (@str_tmp)-1), '来自 t_bm_repeat_purchase GROUP BY 个月');
从 @QQ 中准备 stmt;
执行stmt；
释放准备stmt；

动态查询结果：这并不是我们最终想要的。我们应该放弃这种查询方法，因为前面的数据为空，后面的数据必须整体左移

3使用静态查询

SELECT t.months,< br/> IF(0>num,NULL,SUBSTRING_INDEX(总计, ',', 1)) AS '1',
IF(1>num,NULL,SUBSTRING_INDEX (SUBSTRING_INDEX(总计, ',', 2),',',-1)) AS '2', -- 这是计算第一个数字
IF(2>num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX (total, ',', 3) ,',',-1)) AS '3', -- 取第二个数字
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',< br/> IF(5>num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, ',', 6) ,',',-1)) AS '6',
IF(6> num,NULL,SUBSTRING_INDEX (SUBSTRING_INDEX(总计, ',', 7) ,',',-1)) AS '7',
IF(7>num,NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(总计, ', ', 8) , ',',-1)) AS '8',
IF(8>num,NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(总计, ',', 9) ,',',-1) ) AS '9' ,
IF(9>num,NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(总计, ',', 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(替换(GROUP_CONCAT(a.total),',','')) as num, -- 这是每个月计算的 多少个逗号
GROUP_CONCAT(a.total ORDER BY a.next_months) AS 总计
FROM t_bm_repeat_purchase a
GROUP BY a.months ) t;

选择一个.months，
                                                                                           CHAR_LENGTH( GROUP_CONCAT(a.total))-CHAR_LENGTH(替换(GROUP_CONCAT(a.total),',','' )) as num, -- 这是每月计算的数量 Comma
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(总计, ',', 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(总计, ',', 4) ,',',-1),NULL) AS '4',
IF( num>=5,SUBSTRING_INDEX(SUBSTRING_INDEX(总计, ',', 5) ,',',-1),NULL) AS '5',
IF(num>=6,SUBSTRING_INDEX(SUBSTRING_INDEX(总计, ',') , 6) ,',',-1),NULL) AS '6',
IF(num>=7,SUBSTRING_INDEX(SUBSTRING_INDEX(总计, ',', 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) (总计, ',', 10) ,',',-1),NULL) AS '10',
IF(num>=11,SUBSTRING_INDEX(SUBSTRING_INDEX(总计, ',', 11) ,' ,',-1),NULL) AS '11'
FROM
(SELECT a.months,
COUNT(*) as num, -- 获取每个月有多少条数据
GROUP_CONCAT(a.total ORDER BY a.next_months) AS 总计
FROM t_bm_repeat_purchase a
GROUP BY a.months) t;




4 动态查询和静态查询的优缺点

如果是动态的，我目前还没能得到最终的结果，也是做插入表不方便，但是可以做任意月数，也就是说适合无限数据

如果是静态的，基数不大的话，比如12个月或者6个月，快点上市比较合适。对于大基数来说不方便，但是做插入表比较方便，而且我现在需求需求可以静态做，所以目前采用动态的方式

感谢您仔细阅读本文，希望小编分享《mysql怎么样》这篇文章《实现行到列统计查询》对大家有帮助，同时也希望大家支持我和关注行业资讯频道，更多相关知识等您学习！