时间戳

首页 > 解决方案 > 两次加入一个表并计算记录

sql - 两次加入一个表并计算记录

问题描述

我有 2 张桌子。一个是主人，另一个是查找。TblMstr 和 Cmaster

tblmstr

Id  QuestionId   ChoiceId ChoiceMaster
1    2             1       Strongly Agree
1    3             2       Disagree
1    4             3       Agree
1    5             11       null
2    2             2       Disagree
2    3             5       Disagree
2    4             9       Agree
2    5             12       null

大师

ChoiceId  QuestionId    ChoiceName
1             2         Strongly Agree
2             2         Disagree
3             2         Agree
4             3         Strongly Agree
5             3         Disagree
6             3         Agree     
7             4         Strongly Agree
8             4         Disagree
9             4         Agree  
10            5         Registered Nurse
11            5         Nurse Practitioner
12            5         Pharmacist

我需要让每个问题有多少注册护士有强烈同意、不同意和同意。对于执业护士和药剂师也是如此。

标签： sqlsql-serverdatabasejoin

解决方案

嗯。. . 你可以join两次。一次获得职业，一次获得其他问题的答复：

select co.choicename as occupation,
       sum(case when c.choicename = 'Strongly Agree' then 1 else 0 end) as cnt_strongly_agree,
       sum(case when c.choicename = 'Agree' then 1 else 0 end) as cnt_agree,
       sum(case when c.choicename = 'Disagree' then 1 else 0 end) as cnt_disagree
from Tblmstr m join
     Cmaster co
     on co.id = m.choiceid and co.questionid = 5 join
     Cmaster c
     on co.id = m.choiceid and co.questionid <> 5
group by co.choicename

推荐阅读