請問各位前輩，我有兩個Table: Score、Groups(如下所示)
Table: Score
+------------+------------+------------+
| StudentID | ScoreA | ScoreB |
+------------+------------+------------+
| 1 | 40 | 50 |
+------------+------------+------------+
| 2 | 60 | 40 |
+------------+------------+------------+
| 3 | 75 | 85 |
+------------+------------+------------+
| 4 | 10 | 20 |
+------------+------------+------------+
| 5 | 45 | 55 |
+------------+------------+------------+
| 6 | 55 | 45 |
+------------+------------+------------+
| 7 | 20 | 30 |
+------------+------------+------------+
| 8 | 40 | 40 |
+------------+------------+------------+
Table: Groups
+------------+------------+------------+
| GroupName | ID_START | ID_END |
+------------+------------+------------+
| A | 1 | 3 |
+------------+------------+------------+
| A | 7 | 8 |
+------------+------------+------------+
| B | 4 | 6 |
+------------+------------+------------+
問題一:
我想要用GroupName去列出在這個Group裡面所有學生的成績分數
我的SQL語法如下，如果Groups裡的GroupName是單一筆時，下面SQL語法是work的，
但如果GroupName有兩筆以上時，則無法正常執行，
請各位前輩指點小弟一下，該如何修正？
-----------------------------------------------------------
SELECT * FROM `Score` WHERE
`StudentID` BETWEEN
(SELECT `ID_START` FROM `Groups` WHERE `GroupName`='A')
AND
(SELECT `ID_END` FROM `Groups` WHERE `GroupName`='A')
;
-----------------------------------------------------------
問題二:
我想要用GroupName去統計在每個Group裡面的學生ScoreA分數總和ScoreB分數總和
我的SQL語法如下，不知道是否各位前輩是否有更好的SQL語法？
-------------------------------------------------------------------------
SELECT `Groups`.`GroupName`,SUM(`Score`.`ScoreA`),SUM(`Score`.`ScoreB`)
FROM `Score`,`Groups` WHERE
`Score`.`StudentID` BETWEEN `Groups`.`ID_START` AND `Groups`.`ID_END`
GROUP BY `Groups`.`GroupName`;
-------------------------------------------------------------------------
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 36.239.2.134
※ 編輯: snowfly 來自: 36.239.2.134 (04/14 23:30)