mysql字符串行转列

SELECT 
    product_id,
    SUBSTRING_INDEX(product_attributes, ':', 1) AS color,
    SUBSTRING_INDEX(SUBSTRING_INDEX(product_attributes, ':', -1), ':', 1) AS size
FROM 
    sales;

-- 创建临时表存储拆分后的数据
CREATE TEMPORARY TABLE temp_sales (
    product_id INT,
    attribute VARCHAR(255),
    value VARCHAR(255)
);

-- 将原始数据插入临时表
INSERT INTO temp_sales (product_id, attribute, value)
SELECT 
    product_id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(product_attributes, ':', numbers.n), ':', -1) AS attribute,
    SUBSTRING_INDEX(SUBSTRING_INDEX(product_attributes, ':', numbers.n + 1), ':', -1) AS value
FROM 
    sales,
    (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers
WHERE 
    CHAR_LENGTH(product_attributes) - CHAR_LENGTH(REPLACE(product_attributes, ':', '')) >= numbers.n - 1;

-- 使用临时表进行查询
SELECT 
    product_id,
    MAX(CASE WHEN attribute = 'color' THEN value END) AS color,
    MAX(CASE WHEN attribute = 'size' THEN value END) AS size
FROM 
    temp_sales
GROUP BY 
    product_id;