SQL中如何基于现有数据表生成产品选择is_tap标记列？

这是典型的行转列需求，核心是把每个用户的产品选择记录，转换成以用户ID为行、各产品为列的标记表。下面提供通用跨数据库方案和各主流数据库的专属写法：

通用跨数据库方案（条件聚合） #

这种写法适配MySQL、PostgreSQL、SQL Server、Oracle等几乎所有SQL数据库，无需依赖特定方言函数：

假设原表名为user_product_selection，结构包含id（用户ID）和product（产品名称）两列。执行以下SQL即可生成目标表：

-- 若需要持久化存储结果，执行CREATE TABLE语句
CREATE TABLE user_product_tap_status AS
SELECT
    id,
    -- 针对每个产品生成标记列，存在则为1，否则为0
    MAX(CASE WHEN product = 'A' THEN 1 ELSE 0 END) AS is_tap_A,
    MAX(CASE WHEN product = 'B' THEN 1 ELSE 0 END) AS is_tap_B,
    MAX(CASE WHEN product = 'C' THEN 1 ELSE 0 END) AS is_tap_C
    -- 如有更多产品，直接追加对应的CASE WHEN行即可
FROM user_product_selection
-- 按用户ID分组，合并同一用户的所有选择记录
GROUP BY id;

逻辑说明： #

• CASE WHEN判断当前行是否为目标产品，是则返回1，否则0
• MAX()聚合函数确保同一用户只要选过该产品，最终标记就为1（即使有多条重复选择记录）
• GROUP BY id将同一用户的所有行合并为一行

如果只是需要查询结果而不需要创建新表，直接去掉CREATE TABLE user_product_tap_status AS即可。

主流数据库专属写法（PIVOT函数） #

部分数据库提供了专门的PIVOT语法，写法更简洁：

SQL Server #

SELECT
    id,
    -- 用ISNULL将未选择产品的NULL值转为0
    ISNULL([A], 0) AS is_tap_A,
    ISNULL([B], 0) AS is_tap_B,
    ISNULL([C], 0) AS is_tap_C
FROM (
    -- 子查询构造基础数据：用户ID、产品、标记值1
    SELECT id, product, 1 AS tap_flag
    FROM user_product_selection
) AS source_data
-- PIVOT按product列转置，聚合tap_flag值
PIVOT (
    MAX(tap_flag)
    FOR product IN ([A], [B], [C])
) AS pivot_table;

MySQL 8.0.19+ #

MySQL 8.0.19及以上版本支持PIVOT语法，写法类似：

SELECT
    id,
    COALESCE(`A`, 0) AS is_tap_A,
    COALESCE(`B`, 0) AS is_tap_B,
    COALESCE(`C`, 0) AS is_tap_C
FROM user_product_selection
PIVOT (
    MAX(1)
    FOR product IN ('A' AS `A`, 'B' AS `B`, 'C' AS `C`)
) AS pivot_table;

注意事项 #

• 如果产品列表不固定（需要动态生成列），无法用静态SQL实现，需结合存储过程或脚本拼接SQL语句（比如用GROUP_CONCAT生成所有产品的CASE WHEN行）
• 若原表存在重复的(id, product)记录，可在子查询中添加DISTINCT去重，避免影响结果

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