sql – 计算组内的每个条件

样品数据：

-----------------------------------------------------
 id | ItemId | GroupId | IsGreen | IsRound | IsLoud
----+--------+---------+---------+---------+---------
  1 |  1001  |    1    |    0    |    1    |    1
  2 |  1002  |    1    |    1    |    1    |    0
  3 |  1003  |    2    |    0    |    0    |    0
  4 |  1004  |    2    |    1    |    0    |    1
  5 |  1005  |    2    |    0    |    0    |    0
  6 |  1006  |    3    |    0    |    0    |    0
  7 |  1007  |    3    |    0    |    0    |    0

所期望的结果：

----------------------------------------------------------
 GroupId | TotalRows | TotalGreen | TotalRound | TotalLoud
 --------+-----------+------------+------------+-----------
    1    |     2     |     1      |     2      |     1
    2    |     3     |     1      |     0      |     1
    3    |     2     |     0      |     0      |     0

我使用以下代码来创建表,我遇到的问题是,如果任何组没有与匹配组中没有出现在最终表中的条件之一的行.什么是完成我想做的最好的方法？

SELECT total.GroupId,total.[Count] AS TotalRows,IsGreen.[Count] AS TotalGreen,IsRound.[Count] AS TotalRound,IsLoud.[Count] AS TotalLoud
FROM (
    SELECT GroupId,count(*) AS [Count]
    FROM TestData
    GROUP BY GroupId
) TotalRows
INNER JOIN (
    SELECT GroupId,count(*) AS [Count]
    FROM TestData
    WHERE IsGreen = 1
    GROUP BY GroupId
) IsGreen ON IsGreen.GroupId = TotalRows.GroupId
INNER JOIN (
    SELECT GroupId,count(*) AS [Count]
    FROM TestData
    WHERE IsRound = 1
    GROUP BY GroupId
) IsRound ON IsRound.GroupId = TotalRows.GroupId
INNER JOIN (
    SELECT GroupId,count(*) AS [Count]
    FROM TestData
    WHERE IsLoud = 1
    GROUP BY GroupId
) IsLoud ON IsLoud.GroupId = TotalRows.GroupId

解决方法

select [GroupId],count([GroupId]) as [TotalRows],sum([IsGreen]) as [TotalGreen],sum([IsRound]) as [TotalRound],sum([IsLoud]) as [TotalLoud]
from [TestData]
group by [GroupId]

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!