MySql实现多条数据合并成一条数据,根据多条数据动态生成一条数据列名

原创 : 我们在做项目中有时候会遇到这种需求

《MySql实现多条数据合并成一条数据,根据多条数据动态生成一条数据列名》

需要将name 为张三的hobby合并为如下: 

《MySql实现多条数据合并成一条数据,根据多条数据动态生成一条数据列名》

当然你也可以通过mysql的 group_comcat将hobby合并成一列,那个很简单,这里不做介绍

先建表,插入数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(5) NULL DEFAULT NULL,
  `hobby` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, '张三', 18, '读书');
INSERT INTO `t_user` VALUES (2, '张三', 18, '打游戏');
INSERT INTO `t_user` VALUES (3, '张三', 18, '写代码');

再编写SQL

SELECT  
  GROUP_CONCAT(
    CONCAT(  
      '(select hobby from t_user where id = ',  
      id,  
      ') AS ',  
      CONCAT('hobby',id)
    )  
  ) INTO @sql  
FROM t_user;
SET @sql = CONCAT('select name,age, ', @sql, ' from t_user GROUP BY name');
PREPARE stmt FROM @sql;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt; 

上面的写法其实相当于

select name,age,(select hobby from t_user where id = 1) as hobby1,(select hobby from t_user where id = 2) as hobby2,
(select hobby from t_user where id = 3) as hobby3 from t_user GROUP BY name;

执行结果

《MySql实现多条数据合并成一条数据,根据多条数据动态生成一条数据列名》

以上是表中只有名为张三时的情况

还有当表中存在多个用户时

建表,插数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(5) NULL DEFAULT NULL,
  `hobby` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, '张三', 18, '读书');
INSERT INTO `t_user` VALUES (2, '张三', 18, '打游戏');
INSERT INTO `t_user` VALUES (3, '张三', 18, '写代码');
INSERT INTO `t_user` VALUES (4, '王五', 20, '吃饭');
INSERT INTO `t_user` VALUES (5, '王五', 20, '睡觉');
INSERT INTO `t_user` VALUES (6, '王五', 20, '打豆豆');

《MySql实现多条数据合并成一条数据,根据多条数据动态生成一条数据列名》

因为如果要查询王五的hobby要从1开始,而Mysql不像Oracle有rownum,所以通过sql创建rownum

-- 设置rownum
SELECT @rownum:=@rownum+1 AS rownum, t_user.* 
FROM (SELECT @rownum:=0) r, t_user where name='王五';

然后再查询

set @name = '王五';
SELECT  
  GROUP_CONCAT(DISTINCT 
    CONCAT(  
      '(select hobby from (SELECT @rownum:=@rownum+1 AS rownum, t_user.* 
FROM (SELECT @rownum:=0) r, t_user where name="',@name,'") t where t.rownum = ',  
      t.rownum,  
      ') AS ',  
      CONCAT('hobby',rownum)
    )  
  ) INTO @sql  
FROM (SELECT @rownum:=@rownum+1 AS rownum, t_user.* 
FROM (SELECT @rownum:=0) r, t_user where name=@name) t;
SET @sql = CONCAT('select distinct name,age, ', @sql, ' from t_user where name ="',@name,'"');
PREPARE stmt FROM @sql;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt; 

执行结果

《MySql实现多条数据合并成一条数据,根据多条数据动态生成一条数据列名》

分享完毕

以下为转载:  Mysql 如何通过行列转化动态实现   https://blog.csdn.net/xeseo/article/details/24364921

MYSQL下如何动态生成标题的数据透视表。

先用以下script生成一张包含若干属性的表叫”properties”。

[sql] 
view plain  
copy

  1. CREATE TABLE properties (  
  2.   id INT(11) NOT NULL AUTO_INCREMENT,  
  3.   item_id INT(11) DEFAULT NULL,  
  4.   property_name VARCHAR(255) DEFAULT NULL,  
  5.   value VARCHAR(255) DEFAULT NULL,  
  6.   PRIMARY KEY (id)  
  7. );  
  8. INSERT INTO properties VALUES   
  9.   (1, 1, ‘color’‘blue’),  
  10.   (2, 1, ‘size’‘large’),  
  11.   (3, 1, ‘weight’, 65),  
  12.   (4, 2, ‘color’‘orange’),  
  13.   (5, 2, ‘weight’, 57),  
  14.   (6, 2, ‘size’‘large’),  
  15.   (7, 3, ‘size’‘small’),  
  16.   (8, 3, ‘color’‘red’),  
  17.   (9, 3, ‘weight’, 12),  
  18.   (10, 4, ‘color’‘violet’),  
  19.   (11, 4, ‘size’‘medium’),  
  20.   (12, 4, ‘weight’, 34),  
  21.   (13, 5, ‘color’‘green’),  
  22.   (14, 5, ‘weight’, 10);  

然后我们想根据原始数据生成如下report。

  1. +—-+———+—————+——–+
  2. | id | item_id | property_name | value |
  3. +—-+———+—————+——–+
  4. | 1 | 1 | color | blue |
  5. | 2 | 1 | size | large |
  6. | 3 | 1 | weight | 65 |
  7. | 4 | 2 | color | orange |
  8. | 5 | 2 | weight | 57 |
  9. | 6 | 2 | size | large |
  10. | 7 | 3 | size | small |
  11. | 8 | 3 | color | red |
  12. | 9 | 3 | weight | 12 |
  13. | 10 | 4 | color | violet |
  14. | 11 | 4 | size | medium |
  15. | 12 | 4 | weight | 34 |
  16. | 13 | 5 | color | green |
  17. | 14 | 5 | weight | 10 |
  18. +—-+———+—————+——–+
=>
  1. +———+——–+——–+——–+
  2. | item_id | color | size | weight |
  3. +———+——–+——–+——–+
  4. | 1 | blue | large | 65 |
  5. | 2 | orange | large | 57 |
  6. | 3 | red | small | 12 |
  7. | 4 | violet | medium | 34 |
  8. | 5 | green | NULL | 10 |
  9. +———+——–+——–+——–+

众所周知,MySql里并没有自动表转换的功能。当然,我们可以用一些额外的程序或工具来连接MySQL去执行数据转换。但在这里,我们来探讨下如何手动的写一个查询来实现数据转换。该查询可以这样实现:

[sql] 
view plain  
copy

  1. SELECT  
  2.   item_id,  
  3.   MAX(case when property_name = ‘color’ then VALUE else NULL END) AS color,  
  4.   MAX(CASE property_name WHEN ‘weight’ THEN value ELSE null END) AS weight,  
  5.   MAX(IF(property_name = ‘size’, value, NULL)) AS size,  
  6.   …  
  7.   …  
  8. FROM  
  9.   properties  
  10. GROUP BY  
  11.   item_id;  

很明显,对于任何一个’property_name’我们都做了定义,如’color’, ‘size’。如果属性的类型不变的话,这个查询已经没有问题了。但是如果字段’property_name’经常变,并且可能会增加一个新的,那要怎么处理呢?难道只能每次去改这个查询语句么?这种情况下,就可以使用动态生成查询语句——读取表中字段’property_name’的所有值,根据该值动态的创建一个查询语句。

动态生成查询的实现如下:

[sql] 
view plain  
copy

  1. SET @sql = NULL;  
  2. SELECT  
  3.   GROUP_CONCAT(DISTINCT  
  4.     CONCAT(  
  5.       ‘MAX(IF(property_name = ‘,  
  6.       property_name,  
  7.       ‘, value, NULL)) AS ‘,  
  8.       property_name  
  9.     )  
  10.   ) INTO @sql  
  11. FROM properties;  
  12. SET @sql = CONCAT(‘SELECT item_id, ‘, @sql, ‘ FROM properties GROUP BY item_id’);  

执行该语句,会生成如下的查询语句(为了阅读方便,调整了下格式):

[sql] 
view plain  
copy

  1. SELECT  
  2.   item_id,  
  3.   MAX(IF(property_name = ‘color’, value, NULL)) AS color,  
  4.   MAX(IF(property_name = ‘size’, value, NULL)) AS size,  
  5.   MAX(IF(property_name = ‘weight’, value, NULL)) AS weight  
  6. FROM  
  7.   properties  
  8. GROUP BY  
  9.   item_id  

注意:

MySQL对于GROUP_CONCAT结果用系统变量group_concat_max_len做了限制,默认值是1024。所以,如果你的表有很多列,最好将该值设大一些。

[sql] 
view plain  
copy

  1. SET @@group_concat_max_len = 5000;  
  2. SELECT GROUP_CONCAT(column_name) FROM table;  

经过执行动态生成语句,查询语句被写到变量@sql里,现在我们可以用prepared statment来执行该语句:

[sql] 
view plain  
copy

  1. PREPARE stmt FROM @sql;  
  2. EXECUTE stmt;  
  3. DEALLOCATE PREPARE stmt;  

结果:

  1. +———+——–+——–+——–+
  2. | item_id | color | size | weight |
  3. +———+——–+——–+——–+
  4. | 1 | blue | large | 65 |
  5. | 2 | orange | large | 57 |
  6. | 3 | red | small | 12 |
  7. | 4 | violet | medium | 34 |
  8. | 5 | green | NULL | 10 |
  9. +———+——–+——–+——–+

    原文作者:xuanmingren
    原文地址: https://blog.csdn.net/xuanmingren/article/details/80821155
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞