将MySQL的窄表转换成宽表的方法

小伙伴们有没有觉得学习数据库很有意思？有意思就对了！今天就给大家带来《将MySQL的窄表转换成宽表的方法》，以下内容将会涉及到MySQL，若是在学习中对其中部分知识点有疑问，或许看了本文就能帮到你！

将MySQL的窄表转换成宽表的方法

在扩展设计中，使用窄表可以很方便的增加新的项。如果用宽表，就会需要修改表结构，很不方便。

而使用宽表在查询过滤数据的时候会比窄表方便很多，数据的记录量也会少很多。

这里介绍一种将窄表转置成宽表的语法：

SELECT 
    userkey,
    MAX(CASE tagid
        WHEN 1 THEN valueID
        ELSE NULL
    END) AS tag1,
    MAX(CASE tagid
        WHEN 2 THEN valueID
        ELSE NULL
    END) AS tag2,
    MAX(CASE tagid
        WHEN 3 THEN valueID
        ELSE NULL
    END) AS tag3,
    MAX(CASE tagid
        WHEN 4 THEN valueID
        ELSE NULL
    END) AS tag4,
    MAX(CASE tagid
        WHEN 5 THEN valueID
        ELSE NULL
    END) AS tag5,
    MAX(CASE tagid
        WHEN 6 THEN valueID
        ELSE NULL
    END) AS tag6,
    MAX(CASE tagid
        WHEN 7 THEN tagvalue
        ELSE NULL
    END) AS tag7,
    MAX(CASE tagid
        WHEN 8 THEN tagvalue
        ELSE NULL
    END) AS tag8,
    MAX(CASE tagid
        WHEN 9 THEN tagvalue
        ELSE NULL
    END) AS tag9,
    MAX(CASE tagid
        WHEN 10 THEN tagvalue
        ELSE NULL
    END) AS tag10,
    MAX(CASE tagid
        WHEN 11 THEN tagvalue
        ELSE NULL
    END) AS tag11,
    MAX(CASE tagid
        WHEN 12 THEN tagvalue
        ELSE NULL
    END) AS tag12,
    MAX(CASE tagid
        WHEN 13 THEN tagvalue
        ELSE NULL
    END) AS tag13,
    MAX(CASE tagid
        WHEN 14 THEN tagvalue
        ELSE NULL
    END) AS tag14,
    MAX(CASE tagid
        WHEN 15 THEN tagvalue
        ELSE NULL
    END) AS tag15,
    MAX(CASE tagid
        WHEN 16 THEN tagvalue
        ELSE NULL
    END) AS tag16,
    MAX(CASE tagid
        WHEN 17 THEN tagvalue
        ELSE NULL
    END) AS tag17,
    MAX(CASE tagid
        WHEN 18 THEN tagvalue
        ELSE NULL
    END) AS tag18,
    MAX(CASE tagid
        WHEN 19 THEN tagvalue
        ELSE NULL
    END) AS tag19,
    MAX(CASE tagid
        WHEN 20 THEN tagvalue
        ELSE NULL
    END) AS tag20,
    MAX(CASE tagid
        WHEN 21 THEN tagvalue
        ELSE NULL
    END) AS tag21,
    MAX(CASE tagid
        WHEN 22 THEN tagvalue
        ELSE NULL
    END) AS tag22,
    MAX(CASE tagid
        WHEN 23 THEN tagvalue
        ELSE NULL
    END) AS tag23,
    MAX(CASE tagid
        WHEN 24 THEN valueID
        ELSE NULL
    END) AS tag24,
    MAX(CASE tagid
        WHEN 25 THEN valueID
        ELSE NULL
    END) AS tag25,
    MAX(CASE tagid
        WHEN 26 THEN valueID
        ELSE NULL
    END) AS tag26,
    MAX(CASE tagid
        WHEN 27 THEN valueID
        ELSE NULL
    END) AS tag27,
    MAX(CASE tagid
        WHEN 28 THEN valueID
        ELSE NULL
    END) AS tag28,
    MAX(CASE tagid
        WHEN 29 THEN valueID
        ELSE NULL
    END) AS tag29,
    MAX(CASE tagid
        WHEN 30 THEN valueID
        ELSE NULL
    END) AS tag30,
    MAX(CASE tagid
        WHEN 31 THEN valueID
        ELSE NULL
    END) AS tag31,
    MAX(CASE tagid
        WHEN 32 THEN valueID
        ELSE NULL
    END) AS tag32
FROM
    userdata
WHERE
    tagid IN (1 , 2,
        3,
        4,
        5,
        6,
        7,
        8,
        9,
        10,
        11,
        12,
        13,
        14,
        15,
        16,
        17,
        18,
        19,
        20,
        21,
        22,
        23,
        24,
        25,
        26,
        27,
        28,
        29,
        30,
        31,
        32)
GROUP BY userkey

理论要掌握，实操不能落！以上关于《将MySQL的窄表转换成宽表的方法》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！