连接3个表以从调查中获取详细信息

Question

我做了一个调查，他们有几个领域必须选择。例如:工作场所、工作经验和满意度。

tlDetail
SurveyId QuestionId ChoiceId 
1          1           1
1          2           5
1          3           7
2          1           3
2          2           6
2          3           8

tblquestion
QuestionId Question
1           Question 1
2           Question 2
3           Question 3

TblChoice
ChoiceId QuestionId ChoiceName
1          1          HR
2          1          Accountant
3          1          Teacher
4          2          >10 years
5          2          <10 years
6          2          =10 years
7          3          satisfies
8          3          not satisfied
9          3          no comment

我需要写一个查询，告诉我有多少教师是=10年并且满意的

Answer 1

您可以使用两个级别的聚合：

select count(*)
from (
    select 1 x
    from tlDetail d
    inner join tblChoice c 
        on  c.QuestionId  = d.QuestionId 
        and c.ChoiceId = d.ChoiceId
    group by survey_id
    having
        max(case when c.ChoiceName = 'Teacher' then 1 end) = 1
        and max(case when c.ChoiceName = '=10 years' then 1 end) = 1
) t

请注意，这假设两个不同的问题永远不会有相同的ChoiceName，这对于您的样本数据是可以的，但在现实世界中不一定是这样。如果您想要更准确的答案，那么您需要确定您希望哪个问题的答案是'Teacher'还是'=10 years'，例如：

select count(*)
from (
    select 1 x
    from tlDetail d
    inner join tblChoice c 
        on  c.QuestionId  = d.QuestionId 
        and c.ChoiceId = d.ChoiceId
    inner join tblQuestion q 
        on  q.QuestionId = d.QuestionId
    group by survey_id
    having
        max(case 
            when q.Question = 'Question 1' and c.ChoiceName = 'Teacher' 
            then 1 
        end) = 1
        and max(case 
            when q.Question = 'Question 2' and c.ChoiceName = '=10 years' 
            then 1 
        end) = 1
) t