mysql - 通过使用某些条件在mysql中加入3表？

我有一个任务要用Condition从第1个表（主表）的第3个表中获取一列的计数。
我这里有3张桌子

 **tr_t_assessments**
AssessmentID
Assessmentname
QuestionSetID

**tr_t_questionsetdetails**
QuestionsetID
QuestionID

**tr_t_questions**
QuestionID
Questioname
Difficultylevel

注意：QuestionsetID表示Questionset中没有问题（例如：Emcet Questionset）

Questionid：QuestionSet中的每个Question都有一个ID，称为“ QuestionID”

难度级别：每个问题的难度级别为“低”，“高”，“中”

现在我想为每个QuestionSet的count（DifficultyLevel）DifficultyLevel =“ Low”

我的努力是：

 select
  A.AssessmentID,
  A.AssessName,
  A.ValidFrom,
  A.ValidTo,
  (select count(QuestionID) from tr_t_questionsetdetails where QuestionSetID=A.QuestionSetID) as NoOfQuestions,
  COUNT(C.DifficultyLevel) as Lowlevel,
  from tr_t_assessments A
  inner join tr_t_questionsetdetails B on B.QuestionSetID=A.QuestionSetID
  inner join tr_m_questions C on **C.QuestionID= (??????)** and C.DifficultyLevel='L'

上面的查询点C.QuestionID = A.QuestionID（在主表中不存在）

我怎么得到的。我想为每个Questionset计数（difflevel）。

最佳答案

您可以使用条件汇总来获取计数：

 SELECT a.AssessmentID,
        a.AssessName,
        a.ValidFrom,
        a.ValidTo,
        COUNT(q.QuestionID) AS NoOfQuestions,
        COUNT(CASE WHEN q.DifficultyLevel='Low' THEN 1 END) AS LowLevel,
        COUNT(CASE WHEN q.DifficultyLevel='Medium' THEN 1 END) AS MediumLevel,
        COUNT(CASE WHEN q.DifficultyLevel='High' THEN 1 END) AS HighLevel
FROM    tr_t_assessments AS a
        INNER JOIN tr_t_questionsetdetails AS qsd
            ON qsd.QuestionSetID = a.QuestionSetID
        INNER JOIN tr_m_questions AS q
            ON q.QuestionID = qsd.QuestionID
GROUP BY a.AssessmentId, a.AssessName, a.ValidFrom, a.ValidTo;

所以在这一行：

COUNT(CASE WHEN q.DifficultyLevel='Low' THEN 1 END)

计数功能仅对低难度的问题进行计数（对于其他问题，case语句返回NULL，而这在COUNT函数中未进行计数）。

关于mysql - 通过使用某些条件在mysql中加入3表？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23906458/