SQL Query使用表中的连接对数据进行分组/计数/汇总

I have the following table structure:

我有以下表结构:

I want to end up with a table that contains a count for each combination of GroupId (possible two values) and Cluster (possible two values) for each u.MemberUserSetRef.

我想最终得到一个表,其中包含每个u.MemberUserSetRef的GroupId(可能的两个值)和Cluster(可能的两个值)的每个组合的计数。

If I do this:

如果我这样做:

SELECT        u.MemberUserSetRef, COUNT(r1_1.UserSurveyResultRef) AS count1_1
FROM            [User] AS u INNER JOIN
                         UserSurvey AS s ON u.UserRef = s.UserRef LEFT OUTER JOIN
                         UserSurveyResult AS r1_1 ON s.UserSurveyRef = r1_1.UserSurveyRef
WHERE        (r1_1.GroupId = 'Group1') AND (r1_1.Cluster = 1)
GROUP BY u.MemberUserSetRef

I get three rows, one for each unique MemberUserSetRef, containing the count of UserSurveyResults where GroupId is 'Group1' and Cluster is 1.

我得到三行,每个唯一的MemberUserSetRef一行,包含UserSurveyResults的计数,其中GroupId是'Group1'而Cluster是1。

What I would like to do is return another column (count1_2) for where the GroupId is 'Group1' and Cluster is 2.

我想要做的是返回另一列(count1_2),其中GroupId是'Group1'而Cluster是2。

I tried this:

我试过这个:

SELECT        u.MemberUserSetRef, COUNT(r1_1.UserSurveyResultRef) AS count1_1, COUNT(r1_2.UserSurveyResultRef) AS count1_2
FROM            [User] AS u INNER JOIN
                         UserSurvey AS s ON u.UserRef = s.UserRef RIGHT OUTER JOIN
                         UserSurveyResult AS r1_1 ON s.UserSurveyRef = r1_1.UserSurveyRef RIGHT OUTER JOIN
                         UserSurveyResult AS r1_2 ON s.UserSurveyRef = r1_2.UserSurveyRef
WHERE        (r1_1.GroupId = 'Group1') AND (r1_1.Cluster = 1) AND (r1_2.GroupId = 'Group1') AND (r1_2.Cluster = 2)
GROUP BY u.MemberUserSetRef

But I get no results in this instance.

但是在这个例子中我没有得到任何结果。

Could you please point me in the right direction, so I can get a table that looks like this:

能不能指出我正确的方向,所以我可以得到一张如下表:

MemberUserSetRef|count1_1|count1_2|count2_1|count2_2

containing the appropriate counts for each MemberUserSetRef.

包含每个MemberUserSetRef的适当计数。

Thank you.

2 个解决方案

#1

Select T1.MemberUserSetRef,T1.count1_1,T1.count2_1
From 
    (SELECT u.MemberUserSetRef, COUNT(r1_1.UserSurveyResultRef) AS count1_1
    FROM [User] AS u INNER JOIN
         UserSurvey AS s ON u.UserRef = s.UserRef LEFT OUTER JOIN
         UserSurveyResult AS r1_1 ON s.UserSurveyRef = r1_1.UserSurveyRef
    WHERE (r1_1.GroupId = 'Group1') AND (r1_1.Cluster = 1)
    GROUP BY u.MemberUserSetRef) T1,
    (SELECT u.MemberUserSetRef, COUNT(r1_1.UserSurveyResultRef) AS count1_1
    FROM [User] AS u INNER JOIN
         UserSurvey AS s ON u.UserRef = s.UserRef LEFT OUTER JOIN
         UserSurveyResult AS r1_1 ON s.UserSurveyRef = r1_1.UserSurveyRef
    WHERE (r1_1.GroupId = 'Group1') AND (r1_1.Cluster = 2)
    GROUP BY u.MemberUserSetRef) T2
Where T1.MemberUserSetRef=T2.MemberUserSetRef

#2

You can take a look about GROUPING SETS in detail. CODEPROJECT

您可以详细了解GROUPING SETS。 CodeProject上

SELECT        u.MemberUserSetRef, COUNT(r1_1.UserSurveyResultRef) AS count1_1, COUNT(r1_2.UserSurveyResultRef) AS count1_2
FROM            [User] AS u INNER JOIN
                         UserSurvey AS s ON u.UserRef = s.UserRef RIGHT OUTER JOIN
                         UserSurveyResult AS r1_1 ON s.UserSurveyRef = r1_1.UserSurveyRef RIGHT OUTER JOIN
                         UserSurveyResult AS r1_2 ON s.UserSurveyRef = r1_2.UserSurveyRef
WHERE        (r1_1.GroupId = 'Group1') AND (r1_1.Cluster = 1) AND (r1_2.GroupId = 'Group1') AND (r1_2.Cluster = 2)
GROUP BY GROUPING SETS 
 ((u.MemberUserSetRef), (u.MemberUserSetRef, s.UserRef),
 (u.MemberUserSetRef, s.UserRef, r1_1.UserSurveyRef)
 )