SQL Server：如何合并去重值与计数列并创建小组成员院系分布视图？

问题1：如何在SQL Server中将不同取值及其对应的去重计数放在同一列？ #

在SQL Server 2017及更高版本中，可通过分组统计+字符串聚合的方式实现：

1. 先对目标字段分组，统计每个取值的去重计数（普通计数用COUNT()，去重计数用COUNT(DISTINCT)）；
2. 使用STRING_AGG()函数将每个取值与对应计数拼接成指定格式，最终聚合到同一列。

示例（假设表为YourTable，目标字段为Category，去重依据字段为Id）：

SELECT 
  STRING_AGG(CONCAT(Category, ' - ', CountNum), ', ') AS ResultColumn
FROM (
  SELECT 
    Category,
    COUNT(DISTINCT Id) AS CountNum
  FROM YourTable
  GROUP BY Category
) AS GroupedData

问题2：创建小组成员院系分布视图 #

可以通过多表关联、分组统计结合STRING_AGG()实现需求，完整SQL语句如下：

CREATE VIEW vw_GroupMemberDistribution
AS
SELECT
  g.GroupID,
  g.GroupName,
  STRING_AGG(CONCAT(d.ShortCode, ' - ', dm.DeptMemberCount), ', ') AS MembersDistribution
FROM Groups g
JOIN GroupMembers gm ON g.GroupID = gm.GroupID
JOIN Student s ON gm.StudentID = s.[Student ID]
JOIN Department d ON s.[Department ID] = d.DepartmentID
-- 子查询统计每个小组各院系的成员数
JOIN (
  SELECT
    gm_inner.GroupID,
    s_inner.[Department ID],
    COUNT(*) AS DeptMemberCount
  FROM GroupMembers gm_inner
  JOIN Student s_inner ON gm_inner.StudentID = s_inner.[Student ID]
  GROUP BY gm_inner.GroupID, s_inner.[Department ID]
) dm ON g.GroupID = dm.GroupID AND s.[Department ID] = dm.[Department ID]
-- 按小组分组聚合结果
GROUP BY g.GroupID, g.GroupName
-- 过滤无成员的小组（如示例中的Group-03），需保留则删除此条件
HAVING COUNT(gm.StudentID) > 0

语句说明： #

1. 子查询dm先完成每个小组下各院系的成员数量统计；
2. 关联所有表后，通过STRING_AGG()将院系缩写与人数拼接成目标格式字符串；
3. 若需保留无成员的小组，可移除HAVING条件，并用ISNULL(STRING_AGG(...), '无成员')处理空值情况。

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