计数不同的ID，然后按不同的ID分组，从而产生不正确的计数

问题是你的WHERE条款。它会删除“红色”，“蓝色”和“绿色”以外的所有颜色，因此您无需计算其他颜色。删除该WHERE子句就可以了。

更新：您只想显示至少具有一个红色，绿色或蓝色条目的事件。因此，请添加HAVING子句或将您的查询放入子查询中以使用WHERE。以下是一些选项：

有单独的条件：

SELECT 
  EventId as event,
  COUNT(DISTINCT MicrosoftId) as size,
  SUM(CASE WHEN name = 'red' THEN 1 ELSE 0 END) as red,
  SUM(CASE WHEN name = 'blue' THEN 1 ELSE 0 END) as blue,
  SUM(CASE WHEN name = 'green' THEN 1 ELSE 0 END) as green
FROM TagsMSCV 
GROUP BY EventId
HAVING SUM(CASE WHEN name = 'red' THEN 1 ELSE 0 END) > 0
    OR SUM(CASE WHEN name = 'blue' THEN 1 ELSE 0 END) > 0 
    OR SUM(CASE WHEN name = 'green' THEN 1 ELSE 0 END) > 0 
ORDER BY event;

具有一种复合条件：

SELECT 
  EventId as event,
  COUNT(DISTINCT MicrosoftId) as size,
  SUM(CASE WHEN name = 'red' THEN 1 ELSE 0 END) as red,
  SUM(CASE WHEN name = 'blue' THEN 1 ELSE 0 END) as blue,
  SUM(CASE WHEN name = 'green' THEN 1 ELSE 0 END) as green
FROM TagsMSCV 
GROUP BY EventId
HAVING SUM(CASE WHEN name IN ('red', 'blue', 'green') THEN 1 ELSE 0 END) > 0
ORDER BY event;

在不同条件下的位置：

SELECT *
FROM
(
  SELECT 
    EventId as event,
    COUNT(DISTINCT MicrosoftId) as size,
    SUM(CASE WHEN name = 'red' THEN 1 ELSE 0 END) as red,
    SUM(CASE WHEN name = 'blue' THEN 1 ELSE 0 END) as blue,
    SUM(CASE WHEN name = 'green' THEN 1 ELSE 0 END) as green
  FROM TagsMSCV 
  GROUP BY EventId
) q
WHERE red > 0 OR blue > 0 OR green > 0
ORDER BY event;

在复合条件下：

SELECT *
FROM
(
  SELECT 
    EventId as event,
    COUNT(DISTINCT MicrosoftId) as size,
    SUM(CASE WHEN name = 'red' THEN 1 ELSE 0 END) as red,
    SUM(CASE WHEN name = 'blue' THEN 1 ELSE 0 END) as blue,
    SUM(CASE WHEN name = 'green' THEN 1 ELSE 0 END) as green
  FROM TagsMSCV 
  GROUP BY EventId
) q
WHERE red + blue + green > 0
ORDER BY event;