基于跨列重复值构建唯一角色标识的SQL查询需求

嘿，我来帮你搞定这个SQL查询的需求！先明确下你的核心诉求：你有一张交易表，里面包含了Consumer、Producer、Outsider三个角色列，同一个人在同一交易日期可能同时出现在多个角色列中，你需要把每个用户在该日期下的所有不同角色合并成一个唯一的角色字符串，并且同一个用户在同一天只保留一行记录，对吧？

你的源表结构及数据 #

先把你给出的源表用更清晰的表格展示出来：

期望输出结果 #

你想要的最终输出是这样的（按日期和人名排序更清晰）：

解决方案思路及SQL代码 #

核心思路是先把多列的角色转换为行结构，再去重，最后合并角色，下面给你适配主流数据库的实现方案：

1. MySQL 版本

-- 第一步：将三列角色数据拆分为行结构
WITH unpivoted_data AS (
    SELECT Consumer AS PersonName, 'Consumer' AS Role, TransactionDate
    FROM your_table_name
    UNION ALL
    SELECT Producer AS PersonName, 'Producer' AS Role, TransactionDate
    FROM your_table_name
    UNION ALL
    SELECT Outsider AS PersonName, 'Outsider' AS Role, TransactionDate
    FROM your_table_name
),
-- 第二步：去除同一日期同一人的重复角色
distinct_roles AS (
    SELECT DISTINCT PersonName, Role, TransactionDate
    FROM unpivoted_data
)
-- 第三步：合并角色并输出结果
SELECT 
    PersonName,
    GROUP_CONCAT(Role ORDER BY Role SEPARATOR '-') AS UniqueRole,
    TransactionDate
FROM distinct_roles
GROUP BY PersonName, TransactionDate
ORDER BY TransactionDate, PersonName;

2. SQL Server 版本

把MySQL的GROUP_CONCAT换成SQL Server的STRING_AGG函数即可：

WITH unpivoted_data AS (
    SELECT Consumer AS PersonName, 'Consumer' AS Role, TransactionDate
    FROM your_table_name
    UNION ALL
    SELECT Producer AS PersonName, 'Producer' AS Role, TransactionDate
    FROM your_table_name
    UNION ALL
    SELECT Outsider AS PersonName, 'Outsider' AS Role, TransactionDate
    FROM your_table_name
),
distinct_roles AS (
    SELECT DISTINCT PersonName, Role, TransactionDate
    FROM unpivoted_data
)
SELECT 
    PersonName,
    STRING_AGG(Role, '-') WITHIN GROUP (ORDER BY Role) AS UniqueRole,
    TransactionDate
FROM distinct_roles
GROUP BY PersonName, TransactionDate
ORDER BY TransactionDate, PersonName;

代码逻辑解释

• 拆分行结构：用UNION ALL把原来的Consumer、Producer、Outsider三列转换成每行对应一个人的一个角色，这样方便后续处理。
• 去重：通过DISTINCT确保同一个人在同一日期下的同一个角色不会重复出现（比如如果某人在同一交易里既是Producer又是Producer，只会保留一条）。
• 合并角色：按PersonName和TransactionDate分组，用字符串聚合函数把角色按顺序连接成Role1-Role2的格式，排序角色是为了保证输出的唯一性（比如不会出现Producer-Consumer和Consumer-Producer两种情况）。

备注：内容来源于stack exchange，提问作者TheProgrammer