作者snowfly (@@)
看板Database
标题[SQL ] SQL语法问题(MySQL)
时间Sun Apr 14 23:30:05 2013
请问各位前辈，我有两个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)