使用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