动态列场景下：根据Shelf列值设置对应Stock列值的实现方案

问题概述 #

存在一批动态生成的列，命名以Shelf开头（如ShelfxZN1、ShelfxZD2），每个Shelf类列对应一个以Stock开头、后缀完全相同的列（如StockxZN1、StockxZD2）。列的总数无法预先确定，但命名模式固定。需求为：

• 若某行的Shelf类列存在非空值，将对应的Stock类列值设为0
• 若Shelf类列值为NULL，则不对对应Stock列做任何修改

示例数据 #

原始数据 #

ShelfxZN1,ShelfxZN2,ShelfZD1,ShelfxZD2,StockxZN1,StockxZN2,StockxZD1,StockxZD2
ShelfA,NULL,ShelfC,ShelfD,NULL,NULL,NULL,NULL

转换后数据 #

ShelfxZN1,ShelfxZN2,ShelfZD1,ShelfxZD2,StockxZN1,StockxZN2,StockxZD1,StockxZD2
ShelfA,NULL,ShelfC,ShelfD,0,NULL,0,0

解决方案 #

方案1：Python Pandas（数据文件处理） #

通过动态识别列名，自动匹配Shelf与Stock列对，批量处理：

import pandas as pd
import numpy as np

# 读取输入数据（替换为你的数据源路径/方式）
df = pd.read_csv("your_input_data.csv")

# 筛选所有以Shelf开头的列
shelf_columns = [col for col in df.columns if col.startswith("Shelf")]

# 遍历每一对Shelf-Stock列
for shelf_col in shelf_columns:
    # 生成对应的Stock列名
    stock_col = shelf_col.replace("Shelf", "Stock")
    # 确保Stock列存在，避免报错
    if stock_col in df.columns:
        # 应用条件：Shelf列非空则Stock列设为0，否则保留原值
        df[stock_col] = np.where(df[shelf_col].notna(), 0, df[stock_col])

# 输出处理后的数据（可保存为文件或直接使用）
print(df.to_csv(index=False))

方案2：SQL（数据库内处理） #

如果数据存储在数据库中，可通过动态SQL实现，以MySQL为例：

-- 1. 生成动态处理Stock列的语句片段
SET @sql_stock = '';
SELECT GROUP_CONCAT(
    CONCAT(
        'CASE WHEN `', shelf_col, '` IS NOT NULL THEN 0 ELSE `', stock_col, '` END AS `', stock_col, '`'
    ) SEPARATOR ', '
) INTO @sql_stock
FROM (
    -- 从系统表中获取所有Shelf开头的列，并生成对应Stock列名
    SELECT COLUMN_NAME AS shelf_col, REPLACE(COLUMN_NAME, 'Shelf', 'Stock') AS stock_col
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = '你的数据库名' 
      AND TABLE_NAME = '你的表名' 
      AND COLUMN_NAME LIKE 'Shelf%'
) AS column_pairs;

-- 2. 拼接完整的查询语句（保留所有非Stock列 + 处理后的Stock列）
SET @sql_full = CONCAT(
    'SELECT ',
    -- 获取所有非Stock开头的列
    (SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ', ') 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = '你的数据库名' 
       AND TABLE_NAME = '你的表名' 
       AND COLUMN_NAME NOT LIKE 'Stock%'),
    ', ', @sql_stock, ' FROM 你的表名;'
);

-- 3. 执行动态SQL
PREPARE stmt FROM @sql_full;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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