MySQL 行列转换

最近在慕课上 看MySQL教程 里面关于行转列的教程不错 贴上练习SQL 做个记录
简单行转列

SELECT
    a.user_name,
    sum(b.kills)
FROM
    user1 a
JOIN user_kills b ON a.id = b.user_id
GROUP BY
    user_name;

CROSS JOIN 行列转换

select * from (
    SELECT
        sum(kills) AS '孙悟空'
    FROM
        user1 a
    JOIN user_kills b ON a.id = b.user_id
    AND a.user_name = '孙悟空'
) a
CROSS JOIN (
    SELECT
        sum(kills) AS '沙甥'
    FROM
        user1 a
    JOIN user_kills b ON a.id = b.user_id
    AND a.user_name = '沙甥'
) b
CROSS JOIN (
    SELECT
        sum(kills) AS '猪八戒'
    FROM
        user1 a
    JOIN user_kills b ON a.id = b.user_id
    AND a.user_name = '猪八戒'
) c;

case行列转换

SELECT
    sum(
        CASE
        WHEN user_name = '孙悟空' THEN
            kills
        END
    ) AS '孙悟空',
    sum(
        CASE
        WHEN user_name = '沙甥' THEN
            kills
        END
    ) AS '沙甥',
    sum(
        CASE
        WHEN user_name = '猪八戒' THEN
            kills
        END
    ) AS '猪八戒'
FROM
    user1 a
JOIN user_kills b ON a.id = b.user_id;

单列转多行

SELECT
    user_name,
    REPLACE (
        substring(
            substring_index(mobile, ',', a.id),
            CHAR_LENGTH(
                SUBSTRING_INDEX(mobile, ',', a.id - 1)
            ) + 1
        ),
        ',',
        ''
    ) AS mobile
FROM
    tb_sequence a
CROSS JOIN (
    SELECT
        user_name,
        CONCAT(mobile, ',') AS mobile,
        LENGTH(mobile) - LENGTH(REPLACE(mobile, ',', '')) + 1 size
    FROM
        user1 b
) b ON a.id <= b.size;