使用SQL查询检索特定科目和指定分数的唯一标识符。

Question

想要编写一个SQL查询，以获取数学成绩为80分、英语成绩为70分，并且总分大于等于400分的唯一ID。答案将是id-1和2

ID      Subject Marks
1   Maths   80
1   English 70
1   Hindi   80
1   Science 80
1   SST 90
2   Maths   80
2   English 70
2   Hindi   90
2   Science 80
2   SST 100
3   Maths   80
3   English 60
3   Hindi   90
3   Science 100
3   SST 100

我很困惑如何将两个主题一起作为过滤器插入。

有效的查询 -

SELECT `ID` FROM `ff`
WHERE (`SUBJECT`='MATHS' AND `MARKS`= 80 AND `ID` IN (SELECT `ID` FROM `ff` GROUP BY `ID` HAVING SUM(`MARKS`) >=400) ) OR (`SUBJECT`= 'ENGLISH' AND `MARKS`=70 AND  `ID` IN (SELECT `ID` FROM `ff` GROUP BY `ID` HAVING SUM(`MARKS`) >=400) )

但是这并没有给出预期的结果。

Answer 1

您可以使用条件聚合。

SELECT `ID` 
FROM `ff`
GROUP BY `ID` 
HAVING SUM(`MARKS`) >= 400  
  AND SUM(case when `SUBJECT` = 'English' then `MARKS`end) = 70 
  AND SUM(case when `SUBJECT` = 'Math' then `MARKS` end) = 80