mysql一列值转多行

-- 使用UNION ALL（包含重复行）
SELECT product_id, sales_amount FROM sales WHERE category = 'A'
UNION ALL
SELECT product_id, sales_amount FROM sales WHERE category = 'B';

-- 使用UNION（去除重复行）
SELECT product_id, sales_amount FROM sales WHERE category = 'A'
UNION
SELECT product_id, sales_amount FROM sales WHERE category = 'B';

SELECT 
    product_id,
    SUM(CASE WHEN category = 'A' THEN sales_amount ELSE 0 END) AS sales_A,
    SUM(CASE WHEN category = 'B' THEN sales_amount ELSE 0 END) AS sales_B
FROM sales
GROUP BY product_id;

-- 假设有一个包含JSON数据的表
CREATE TABLE products (
    id INT PRIMARY KEY,
    details JSON
);

-- 插入示例数据
INSERT INTO products (id, details) VALUES
(1, '{"name": "Product A", "price": 100, "categories": ["Electronics", "Gadgets"]}'),
(2, '{"name": "Product B", "price": 200, "categories": ["Clothing", "Accessories"]}');

-- 使用JSON_TABLE展开JSON数据
SELECT p.id, jt.category
FROM products p
JOIN JSON_TABLE(p.details, '$.categories[*]' COLUMNS(category VARCHAR(255) PATH '$')) jt;