SQL查询基于条件

Question

我有三个相关的表计算值：

Evaluations: 
EvalID 
WorktypeID 
PointsPossible 

Items: 
EvalID 
ItemID 
Value 
QuestionID 

Question_Worktype: 
WorkTypeID 
QuestionID 
Points 

有每个评估多个项目，每个项目都有一个QuestionID/WorkTypeID一对涉及Question_Worktype，如果一个条目得分在于。

Items.Value是项目是否正确。

我想查询返回的所有项目的总点数为评价点止被授予如果该项目的值为1

我的目标是获得：

Eval_ID, Total Points (calculated), Points Possible 

我似乎无法包围我的大脑。

编辑：我忘了一个重要的部分：

我有一个额外的表：

qa_edits: 
EditID 
ItemID 
NewValue 
Date 

如果有人编辑值，它存放在这里，我真的需要它来计算基于如果有最新的项目编辑，或者项目值没有。

Answer 1

开始你的编辑表，让每个项目的最新编辑：

SELECT 
    M.ItemId, 
    COALESCE(Q.Value, I.Value) as Value, 
    I.QuestionId 
FROM (
    SELECT 
     I.ItemId, 
     MAX(Date) as LastEditDate 
    FROM Items as I 
    LEFT OUTER JOIN QA_Edits as E ON 
     I.ItemId = E.ItemId 
    GROUP BY 
     I.ItemId 
) as M 
LEFT OUTER JOIN QA_Edits as Q ON 
    M.ItemId = Q.ItemId 
    AND M.LastEditDate = Q.Date 
LEFT OUTER JOIN Items as I ON 
    M.ItemId = Q.ItemId 
    AND M.LastEditDate IS NULL 

我建议一个视图，而且 - 我们姑且称之为它LatestItems。

然后，回Blorgbeard's：

select 
    e.Eval_ID, 
    sum(qw.Points) as TotalPoints, 
    e.PointsPossible 
from Evaluations e 
join LatestItems i on 
    e.Eval_ID = i.Eval_ID 
    and i.Value = 1 
join Questions_WorkType qw on 
    qw.QuestionID = i.QuestionID 
group by 
    e.Eval_ID, e.PointsPossible 

Answer 2

我认为这应该工作：

select e.Eval_ID, sum(qw.Points) as TotalPoints, e.PointsPossible 
    from Evaluations e 
    left join Items i on e.Eval_ID = i.Eval_ID and i.Value = 1 
    join Questions_WorkType qw on qw.QuestionID = i.QuestionID 
    group by e.Eval_ID, e.PointsPossible