如何将SQL中含Y/N值的列按编码表转换为新code列？

首先假设feature_codes表结构为feature_name（存储特征名如'sedan'/'ABS'/'gasoline'）和code（对应编码），以下分两种需求给出适配主流数据库的SQL写法：

1. 给cars表新增code列并填充特征编码 #

步骤1：新增code列 #

先执行ALTER语句添加列（根据实际需求调整字段长度）：

-- MySQL/SQL Server/Oracle通用
ALTER TABLE cars ADD COLUMN code VARCHAR(100);

步骤2：填充编码（按数据库类型选择） #

场景A：将所有Y对应的编码拼接为单个字符串（如"S1, A2"）

• MySQL：

UPDATE cars c
SET code = (
  SELECT GROUP_CONCAT(fc.code SEPARATOR ', ')
  FROM (
    SELECT 'sedan' AS feature_name UNION ALL
    SELECT 'ABS' UNION ALL
    SELECT 'gasoline'
  ) AS features
  JOIN feature_codes fc ON features.feature_name = fc.feature_name
  WHERE (features.feature_name = 'sedan' AND c.sedan = 'Y')
     OR (features.feature_name = 'ABS' AND c.ABS = 'Y')
     OR (features.feature_name = 'gasoline' AND c.gasoline = 'Y')
);

• SQL Server/PostgreSQL：

UPDATE c
SET code = (
  SELECT STRING_AGG(fc.code, ', ')
  FROM (
    SELECT 'sedan' AS feature, c.sedan AS val
    UNION ALL
    SELECT 'ABS', c.ABS
    UNION ALL
    SELECT 'gasoline', c.gasoline
  ) AS unpivoted
  JOIN feature_codes fc ON unpivoted.feature = fc.feature_name
  WHERE unpivoted.val = 'Y'
)
FROM cars c;

• Oracle：

UPDATE cars c
SET code = (
  SELECT LISTAGG(fc.code, ', ') WITHIN GROUP (ORDER BY fc.feature_name)
  FROM (
    SELECT 'sedan' AS feature_name FROM DUAL UNION ALL
    SELECT 'ABS' FROM DUAL UNION ALL
    SELECT 'gasoline' FROM DUAL
  ) AS features
  JOIN feature_codes fc ON features.feature_name = fc.feature_name
  WHERE (features.feature_name = 'sedan' AND c.sedan = 'Y')
     OR (features.feature_name = 'ABS' AND c.ABS = 'Y')
     OR (features.feature_name = 'gasoline' AND c.gasoline = 'Y')
);

2. 创建仅含car和code列的新表 #

场景A：每个Y特征对应一行记录（如某车有2个Y特征则生成2行） #

-- 通用写法，适配多数数据库
CREATE TABLE car_features AS
SELECT c.car, fc.code
FROM cars c
JOIN feature_codes fc ON fc.feature_name = 'sedan' AND c.sedan = 'Y'
UNION ALL
SELECT c.car, fc.code
FROM cars c
JOIN feature_codes fc ON fc.feature_name = 'ABS' AND c.ABS = 'Y'
UNION ALL
SELECT c.car, fc.code
FROM cars c
JOIN feature_codes fc ON fc.feature_name = 'gasoline' AND c.gasoline = 'Y';

场景B：每个车对应一行，code为所有Y特征编码的拼接字符串 #

-- MySQL示例，其他数据库替换拼接函数即可
CREATE TABLE car_codes AS
SELECT c.car,
       (SELECT GROUP_CONCAT(fc.code SEPARATOR ', ')
        FROM (
          SELECT 'sedan' AS feature_name UNION ALL
          SELECT 'ABS' UNION ALL
          SELECT 'gasoline'
        ) AS features
        JOIN feature_codes fc ON features.feature_name = fc.feature_name
        WHERE (features.feature_name = 'sedan' AND c.sedan = 'Y')
           OR (features.feature_name = 'ABS' AND c.ABS = 'Y')
           OR (features.feature_name = 'gasoline' AND c.gasoline = 'Y')) AS code
FROM cars c;

常见错误说明 #

你之前用JOIN+CASE出错，大概率是没正确处理列转行逻辑——原cars表的特征是列存储，需要先通过UNION ALL将列转为行，再和feature_codes关联，直接用CASE+JOIN容易出现笛卡尔积或匹配逻辑错误。

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