如何将字符串列转换为多列二进制列以实现复杂条件查询

我来帮你搞定这个问题！咱们分两步走：先把你的原始长表转换成带二进制标记的宽表，再实现你需要的查询逻辑。

一、将长表转换为二进制标记宽表 #

你想要的宽表核心是按ref聚合，用1/0标记每个value和sub_value是否存在，用SQL的GROUP BY配合CASE WHEN就能轻松实现：

SELECT
  ref AS unique_ref,
  -- 标记value系列是否存在
  MAX(CASE WHEN value = 'value1' THEN 1 ELSE 0 END) AS value1,
  MAX(CASE WHEN value = 'value2' THEN 1 ELSE 0 END) AS value2,
  MAX(CASE WHEN value = 'value3' THEN 1 ELSE 0 END) AS value3,
  MAX(CASE WHEN value = 'value4' THEN 1 ELSE 0 END) AS value4,
  -- 标记sub_value系列是否存在
  MAX(CASE WHEN sub_value = 'sub1' THEN 1 ELSE 0 END) AS sub1,
  MAX(CASE WHEN sub_value = 'sub2' THEN 1 ELSE 0 END) AS sub2,
  MAX(CASE WHEN sub_value = 'sub3' THEN 1 ELSE 0 END) AS sub3
FROM your_table_name -- 替换成你的表名
GROUP BY ref;

逻辑解释： #

• GROUP BY ref：把同一个ref的所有行聚合到一起
• CASE WHEN：对每一行判断是否匹配目标值，匹配返回1，否则0
• MAX()：只要该ref下有至少一行匹配，就会保留1（因为1比0大），完美实现“存在即标记1”的需求

如果你的value或sub_value可能值很多，手动写CASE WHEN太麻烦，可以用动态SQL自动生成所有列（以MySQL为例）：

-- 自动生成value列的CASE语句
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('MAX(CASE WHEN value = ''', value, ''' THEN 1 ELSE 0 END) AS ', value)
) INTO @value_columns
FROM your_table_name;

-- 自动生成sub_value列的CASE语句
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('MAX(CASE WHEN sub_value = ''', sub_value, ''' THEN 1 ELSE 0 END) AS ', sub_value)
) INTO @sub_columns;

-- 拼接并执行完整的宽表查询
SET @sql = CONCAT(
  'SELECT ref AS unique_ref, ', @value_columns, ', ', @sub_columns, ' FROM your_table_name GROUP BY ref;'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

二、实现目标查询：找符合条件的唯一ref #

你需要的是同时包含value1和value2，或者包含sub1的唯一ref，这里有两种实现方式：

方式1：直接用原表查询（无需提前转宽表） #

这种方式适合临时查询，不用额外生成宽表，效率也不错：

SELECT DISTINCT ref
FROM your_table_name
WHERE
  -- 条件1：该ref同时存在value1和value2
  (EXISTS (SELECT 1 FROM your_table_name t2 WHERE t2.ref = your_table_name.ref AND t2.value = 'value1')
   AND EXISTS (SELECT 1 FROM your_table_name t3 WHERE t3.ref = your_table_name.ref AND t3.value = 'value2'))
  -- 条件2：该ref存在sub1（和条件1是或关系）
  OR EXISTS (SELECT 1 FROM your_table_name t4 WHERE t4.ref = your_table_name.ref AND t4.sub_value = 'sub1');

方式2：用转换好的宽表查询 #

如果需要多次执行这类查询，提前生成宽表会更高效：

SELECT unique_ref
FROM (
  -- 这里嵌入上面的宽表转换SQL
  SELECT
    ref AS unique_ref,
    MAX(CASE WHEN value = 'value1' THEN 1 ELSE 0 END) AS value1,
    MAX(CASE WHEN value = 'value2' THEN 1 ELSE 0 END) AS value2,
    MAX(CASE WHEN sub_value = 'sub1' THEN 1 ELSE 0 END) AS sub1
  FROM your_table_name
  GROUP BY ref
) AS wide_table
WHERE (value1 = 1 AND value2 = 1) OR sub1 = 1;

两种方式都能得到你想要的结果，根据你的使用场景选就行～

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