SQL视图中活动列聚合值重复的技术问题排查求助

我搭建了一套用户档案数据库表体系，创建了View_Profile_Information视图展示用户核心档案信息（姓名、邮箱、关注数、粉丝数、参与活动列表等），但发现Activities字段存在重复——每个活动名称本该只显示一次，实际却重复出现多次（末尾NULL为预期设置）。

当前视图输出异常示例 #

相关表结构与数据 #

Activity表（活动名称映射）

User_Activity表（用户-活动关联）

原视图SQL代码 #

CREATE VIEW [View_Profile_Information] AS
SELECT U.MemberID, 
U.FirstName, 
U.LastName, 
L.Town, 
L.County, 
L.Country, 
U.Email, 
U.AboutMe, 
U.DistanceUnit, 
U.ActivityTimePreference, 
U.Weight,        
U.Height, 
STRING_AGG(A.ActivityName, ', ') AS Activities, 
COUNT(DISTINCT UF.Follows_MemberID) AS TotalFollowing, 
COUNT(DISTINCT UF2.This_MemberID) AS TotalFollowers
FROM CW1.[User] U

LEFT JOIN CW1.[Location] L
ON U.LocationID = L.LocationID

LEFT JOIN CW1.[User_Activity] UA
ON U.MemberID = UA.MemberID    
              
LEFT JOIN CW1.[Activity] A
ON UA.ActivityID = A.ActivityID  

LEFT JOIN CW1.[User_Follows] UF 
ON U.MemberID = UF.This_MemberID

LEFT JOIN CW1.[User_Follows] UF2
ON U.MemberID = UF2.Follows_MemberID

GROUP BY U.MemberID, U.FirstName, LastName, Town, County, Country, Email, AboutMe, DistanceUnit, ActivityTimePreference, Weight, 
       Height

问题根源 #

直接关联User_Follows表会产生笛卡尔积：用户的每条活动记录会和每条关注/粉丝记录进行关联，导致活动名称被重复输出，重复次数等于该用户的关注数×粉丝数（或其中之一，取决于关联逻辑）。虽然COUNT(DISTINCT)修正了关注/粉丝的统计数，但STRING_AGG会把所有重复的活动名称都拼接进去。

解决方案 #

提供两种常用修复方式，按需选择：

方式1：在STRING_AGG中使用DISTINCT（SQL Server 2017+）

直接在STRING_AGG中添加DISTINCT关键字，对活动名称去重后再拼接：

CREATE VIEW [View_Profile_Information] AS
SELECT U.MemberID, 
U.FirstName, 
U.LastName, 
L.Town, 
L.County, 
L.Country, 
U.Email, 
U.AboutMe, 
U.DistanceUnit, 
U.ActivityTimePreference, 
U.Weight,        
U.Height, 
STRING_AGG(DISTINCT A.ActivityName, ', ') AS Activities, -- 添加DISTINCT去重
COUNT(DISTINCT UF.Follows_MemberID) AS TotalFollowing, 
COUNT(DISTINCT UF2.This_MemberID) AS TotalFollowers
FROM CW1.[User] U

LEFT JOIN CW1.[Location] L
ON U.LocationID = L.LocationID

LEFT JOIN CW1.[User_Activity] UA
ON U.MemberID = UA.MemberID    
              
LEFT JOIN CW1.[Activity] A
ON UA.ActivityID = A.ActivityID  

LEFT JOIN CW1.[User_Follows] UF 
ON U.MemberID = UF.This_MemberID

LEFT JOIN CW1.[User_Follows] UF2
ON U.MemberID = UF2.Follows_MemberID

GROUP BY U.MemberID, U.FirstName, LastName, Town, County, Country, Email, AboutMe, DistanceUnit, ActivityTimePreference, Weight, 
       Height

方式2：子查询提前统计，避免笛卡尔积（兼容低版本）

通过子查询单独计算用户的活动列表、关注数和粉丝数，再与主表关联，从根源避免多表关联产生的笛卡尔积：

CREATE VIEW [View_Profile_Information] AS
SELECT 
    U.MemberID, 
    U.FirstName, 
    U.LastName, 
    L.Town, 
    L.County, 
    L.Country, 
    U.Email, 
    U.AboutMe, 
    U.DistanceUnit, 
    U.ActivityTimePreference, 
    U.Weight,        
    U.Height, 
    COALESCE(Act.Activities, 'NULL') AS Activities,
    COALESCE(Follow.TotalFollowing, 0) AS TotalFollowing,
    COALESCE(Follow.TotalFollowers, 0) AS TotalFollowers
FROM CW1.[User] U
LEFT JOIN CW1.[Location] L ON U.LocationID = L.LocationID

-- 子查询获取用户去重后的活动列表
LEFT JOIN (
    SELECT 
        UA.MemberID,
        STRING_AGG(A.ActivityName, ', ') AS Activities
    FROM CW1.[User_Activity] UA
    LEFT JOIN CW1.[Activity] A ON UA.ActivityID = A.ActivityID
    GROUP BY UA.MemberID
) Act ON U.MemberID = Act.MemberID

-- 子查询获取用户的关注数和粉丝数
LEFT JOIN (
    SELECT
        This_MemberID,
        COUNT(DISTINCT Follows_MemberID) AS TotalFollowing,
        (SELECT COUNT(DISTINCT This_MemberID) FROM CW1.[User_Follows] WHERE Follows_MemberID = uf.This_MemberID) AS TotalFollowers
    FROM CW1.[User_Follows] uf
    GROUP BY This_MemberID
) Follow ON U.MemberID = Follow.This_MemberID

说明 #

• 方式1代码简洁，适合SQL Server 2017及以上版本；
• 方式2兼容性更强，同时避免了笛卡尔积带来的性能损耗，数据量大时优势更明显。

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