美文网首页
mysql列转行

mysql列转行

作者: 潦倒神仙 | 来源:发表于2020-03-05 16:50 被阅读0次

    需求:将表

    id column
    1 A,B,C
    2 D,E

    转为表

    id column
    1 A
    1 B
    1 C
    2 D
    2 E

    新建表:test

    DROP TABLE IF EXISTS `test`;
    CREATE TABLE `test`  (
      `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
      `c` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL
    ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    INSERT INTO `test` VALUES ('1', 'A,B,C');
    INSERT INTO `test` VALUES ('2', 'D,E');
    

    新建表:seq

    DROP TABLE IF EXISTS `seq`;
    CREATE TABLE `seq`  (
      `id` int(11) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    INSERT INTO `seq` VALUES (1);
    INSERT INTO `seq` VALUES (2);
    INSERT INTO `seq` VALUES (3);
    INSERT INTO `seq` VALUES (4);
    

    如图所示:


    seq.png test.png

    运行sql

    SELECT
        t.id,
        SUBSTRING_INDEX( SUBSTRING_INDEX( t.c, ',', s.id ), ',', - 1 ) 
    FROM
        test t
        JOIN seq s ON s.id <= ( LENGTH( t.c ) - LENGTH( REPLACE ( t.c, ',', '' ) ) + 1 )
    
    

    可得


    结果.png

    相关文章

      网友评论

          本文标题:mysql列转行

          本文链接:https://www.haomeiwen.com/subject/yzfqrhtx.html