如何在SQL数据表中实现列数据转移合并并插入指定产品行

假设你的表名为products，可按以下步骤操作：

方案1：修改原表得到目标结构 #

步骤1：更新指定商品的Value数值 #

UPDATE products
SET Value = 200
WHERE marketId = 2 AND Product = 'air condition';

UPDATE products
SET Value = 550
WHERE marketId = 2 AND Product = 'refrigerator';

步骤2：为每个marketId插入Phone行 #

INSERT INTO products (marketId, Product, Value)
SELECT DISTINCT marketId, 'Phone', NULL
FROM products;

步骤3：删除冗余的Phone列（操作前请备份数据） #

ALTER TABLE products
DROP COLUMN Phone;

方案2：不修改原表，直接查询得到目标结果 #

如果不需要改动原表结构，可直接执行SELECT语句得到你要的结果：

SELECT marketId, Product, Value
FROM (
    SELECT 
        marketId,
        Product,
        CASE
            WHEN marketId = 2 AND Product = 'air condition' THEN 200
            WHEN marketId = 2 AND Product = 'refrigerator' THEN 550
            ELSE Value
        END AS Value
    FROM products
    UNION ALL
    SELECT DISTINCT marketId, 'Phone', NULL
    FROM products
) AS result
ORDER BY marketId, Product;

内容的提问来源于stack exchange，提问作者Neca24