多表查询SQL中的多个计数

I would like to count The different requests by survey Id's and grouping it by SubjectValue

我想通过调查ID来计算不同的请求，并按SubjectValue对其进行分组

I have done this on just the one table with a sub query, but I'm not too sure to do it with several. Could anyone help me out?

我在一个带有子查询的表上完成了这个，但是我不太确定要用几个。任何人都可以帮我吗？

This is how the 3 tables are joined. The only values of note are

这就是3个表的连接方式。值得注意的唯一值

subjectValue - Table A
Request_Id - Table A
Survey_Id - Table C

SELECT     TableA.SubjectValue
FROM          TableB INNER JOIN
                       TableA ON TableB.ID = TableA.Request_ID INNER JOIN
                       Table C ON TableB.Details_ID = TableC.ID

May I also add that all counts should be returned in the same row.

我还要补充说，所有计数都应该在同一行中返回。

there are 3 different survey Id's so the count will need a where clause on the survey_id.

有3个不同的调查ID，因此计数需要在survey_id上有where子句。

Hope that makes sense.

希望有道理。

Many thanks in advance.

提前谢谢了。

2 个解决方案

#1

You can use generic Cross-tab method

您可以使用通用的交叉表方法

select 
    TableA.SubjectValue,
    SUM(case when somecol='request1' then 1 else 0 end) as request1,
    SUM(case when somecol='request2' then 1 else 0 end) as request2,
    .
TableB INNER JOIN
                       TableA ON TableB.ID = TableA.Request_ID INNER JOIN
                       Table C ON TableB.Details_ID = TableC.ID
group by
    TableA.SubjectValue

#2

You probably dont need to join to table C (surveys) assuming you have a foreign key to it on table B (Requests).

假设您在表B（请求）上有一个外键，您可能不需要加入表C（调查）。

try this.

尝试这个。

SELECT TableA.SubjectValue, COUNT(TableB.SurveyID)
FROM TableB 
INNER JOIN TableA ON TableB.ID = TableA.Request_ID 
Group by TableA.SubjectValue

EDIT: To Include SurveyID use this..

编辑：包含SurveyID使用此..

SELECT      TableC.SurveyID, TableA.SubjectValue, COUNT(TableB.RequestId)
FROM        TableA 
INNER JOIN  TableB ON TableB.SurveyID = TableA.SurveyID 
INNER JOIN  TableC ON TableC.RequestID = TableB.RequestID
Group by TableA.SubjectValue, TableC.SurveyID

(hope i didnt get my A, B's and C's mixed up.)

（希望我没有把我的A，B和C混在一起。）

#1