结合两个小查询（该组由不同的值）到一个查询

Question

请看看下面的表（称为响应）举行。它显示了受访者对问题和答案的回应。

questionid answerid respondentid 
     1   10  1 
     1   11  2 
     1   11  4 
     1   12  3 
     1   12  5 
     2   20  1 
     2   20  2 
     2   21  2 
     2   22  1 
     2   22  4 
     2   23  1 
     2   23  3 
     2   24  4 
     3   30  2 
     3   30  3 
     3   30  4 
     3   31  1 

我们可以运行下面的SQL：

select questionid, answerid, count(respondentid) as noOfRespondentsToQuestionAndAnswer 
from response 
group by questionid, answerid 

...这将告诉我们许多受访者如何回答问题+答案的每个组合。

我们也可以这样做：

select questionid, count(distinct respondentid) as noOfRespondentsToQuestion 
from response 
group by questionid 

...这将告诉我们许多不同的受访者是如何回答每一个问题。

我想将这两个选择合并为一个，并让每个问题（这将是必要的，因为它只基于问题而不是回答）的不同答复者的数目在多于一行上表示。

所以，我想类似下面的结果：

questionid,answerid,noOfRespondentsToQuestionAndAnswer,noOfRespondentsToQuestion 
1 10 1 5 
1 11 2 5 
1 12 2 5 
2 20 2 4 
2 21 1 4 
2 22 2 4 
2 23 2 4 
2 24 1 4 
3 30 3 4 
3 31 1 4 

是否有可能只用一个查询来达致这？

Answer 1

没有指定数据库的哪一类，这将简化这个，但是从纯粹的SQL的想法，不使用任何分析，是可以做到的，但你将失去效率。

select questionid, answerid, 
(select a.count(*) FROM datatable a WHERE a.questionid=questionid AND a.answerid=answerid), 
(select b.count(*) FROM datatable b WHERE b.questionid=questionid) 
FROM datatable ORDER BY questionid, answerid; 

Answer 2

select one.questionid, answerid, 
     noOfRespondentsToQuestionAndAnswer, 
     noOfRespondentsToQuestion 
FROM (
select questionid, answerid, 
     count(respondentid) as noOfRespondentsToQuestionAndAnswer 
from response 
group by questionid, answerid) AS one 
JOIN (
select questionid, count(distinct respondentid) as noOfRespondentsToQuestion 
from response 
group by questionid) AS two 
WHERE one.questionid = two.questionid;