Question

我有以下数据库设计:

Employee Table: EmployeeID, Name, OrgCode
Department Table: OrgCode, DepartName
CompleteSurvey Table: ID, RespondantID, QuestionsAnswersID
Questions Table: QuestionID, Question
Answers Table: AnswerID, Answer
QuestionsAnswers Table: ID, QuestionID, AnswerID

我写了一个查询,用所有可能的答案来显示每个问题,以及每个问题参与者的总数,即使有一个可能的答案,没有任何参与者参加,结果将在每个部门显示。

我现在必须做的是修改这一查询,以显示每个部门的雇员总数,除了我已经获得的参加总人数之外,还显示每个部门的雇员总数,然后显示参与百分比,即参加总人数/雇员总数。

" 强 ",如何做到这一点? " /强 "

查询:

SELECT     
   TOP (100) PERCENT 
   COUNT(DISTINCT CompleteSurvey.RespondantID) AS [Total Number of Participants], 
   dbo.Answers.Answer, dbo.Questions.Question, 
   dbo.Departments.DepartmentName
FROM
   dbo.Questions 
INNER JOIN
   dbo.QuestionsAnswers ON dbo.Questions.QuestionID = dbo.QuestionsAnswers.QuestionID 
INNER JOIN
   dbo.Answers ON dbo.QuestionsAnswers.AnswerID = dbo.Answers.AnswerID 
CROSS JOIN
   dbo.Departments 
LEFT OUTER JOIN
   (SELECT     
        dbo.Employees.OrgCode, CompleteSurvey_1.QuestionsAnswersID, 
        CompleteSurvey_1.RespondantID
    FROM          
        dbo.CompleteSurvey AS CompleteSurvey_1 
    INNER JOIN
        dbo.Employees ON dbo.Employees.EmployeeID = CompleteSurvey_1.RespondantID) AS CompleteSurvey ON dbo.QuestionsAnswers.ID = CompleteSurvey.QuestionsAnswersID AND dbo.Departments.OrgCode = CompleteSurvey.OrgCode
GROUP BY 
    dbo.Answers.Answer, dbo.Questions.Question, dbo.Departments.DepartmentName
ORDER BY 
    dbo.Questions.Question, dbo.Answers.Answer, dbo.Departments.DepartmentName

Answer 1

我想你现在有的就是这个

""https://i.sstatic.net/Jw1ru.png" alt="此处输入图像描述"/ >

所以,试试这个

;with
q_00 as ( -- all possible QA combinations
    select
          x.ID        as QA_ID
        , q.Question
        , a.Answer
    from QuestionsAnswers    as x
    join Questions           as q on q.QuestionID = x.QuestionID
    join Answers             as a on a.AnswerID   = x.AnswerID  
),
q_01 as ( -- QA chosen by some employees 
    select
          s.QuestionAnswersID as QA_ID
        , e.EmployeeID
        , d.DepartmentName
    from  CompleteSurvey as s 
    join  Employee       as e on e.EmployeeID = s.RespondantID
    join  Department     as d on d.OrgCode    = e.OrgCode
),
q_02 as ( -- participants for each QA for each department
    select
          Question
        , Answer
        , DepartmentName
        , count (distinct EmployeeID) as Participants
    from      q_00 as a
    left join q_01 as b on b.QA_ID = a.QA_ID
    group by Question, Answer, DepartmentName
),
q_03 as ( -- number of people in a department 
    select
          DepartmentName
        , count(1)    as PeopleInDepartment
    from Department as d
    join Employee   as e on e.OrgCode = d.OrgCode
    group by DepartmentName
)
select
      Question
    , Answer
    , a.DepartmentName
    , Participants
    , PeopleInDepartment
    , cast(
            cast(Participants       as decimal(9,2))
          / cast(PeopleInDepartment as decimal(9,2))
          * 100.0
          ) as ParticipationPercent
from q_02 as a
join q_03 as b on b.DepartmentName = a.DepartmentName
order by Question, Answer, a.DepartmentName
;

您可以通过丢弃这两个( 无效的) < code> IDs 并坚持ID命名公约来清理一下。这样您就不会需要关于 < codeçasues Answers 和 < code> CompleteSureve 的独特索引。因此, 它可能看起来是这样的。

""https://i.sstatic.net/PAbWc.png" alt="此处的内置图像描述"/"

友情链接