SQL子查询返回多个行错误
SQL子查询返回多个行错误
提问于 2017-03-10 06:16:25
我有一个班级的作业，要求我用SQL编写一个过程，获取原始的HW和测试分数，然后计算每个作业的百分比以及总的加权平均值。表中的主键是学生的SSN。每个分配的最大分数存储在SSN = '0001‘下，分配的权重存储在SSN =’0002‘下。我得到了一个“子查询返回多行”的错误，我不确定我错在哪里。如果有人能帮我，我会很感激的。
SET hw1M = (SELECT hw1 FROM RAW_SCORES WHERE (SSN = '0001'));
SET hw1W = (SELECT hw1 FROM RAW_SCORES WHERE (SSN = '0002'));
SET hw2aM = (SELECT hw2a FROM RAW_SCORES WHERE (SSN = '0001'));
SET hw2aW = (SELECT hw2a FROM RAW_SCORES WHERE (SSN = '0002'));
SET hw2bM = (SELECT hw2b FROM RAW_SCORES WHERE (SSN = '0001'));
SET hw2bW = (SELECT hw2b FROM RAW_SCORES WHERE (SSN = '0002'));
SET MidtermM = (SELECT Midterm FROM RAW_SCORES WHERE (SSN = '0001'));
SET MidtermW = (SELECT Midterm FROM RAW_SCORES WHERE (SSN = '0002'));
SET hw3M = (SELECT HW3 FROM RAW_SCORES WHERE (SSN = '0001'));
SET hw3W = (SELECT HW3 FROM RAW_SCORES WHERE (SSN = '0002'));
SET FExamM = (SELECT FExam FROM RAW_SCORES WHERE (SSN = '0001'));
SET FExamW = (SELECT FExam FROM RAW_SCORES WHERE (SSN = '0002'));
SET HW1Pct = ((SELECT Hw1 FROM RAW_SCORES)/hw1M);
SET HW2aPct = ((SELECT Hw2a FROM RAW_SCORES)/hw2aM);
SET HW2bPct = ((SELECT Hw2b FROM RAW_SCORES)/hw2bM);
SET MidtermPct = ((SELECT Midterm FROM RAW_SCORES)/MidtermM);
SET HW3Pct = ((SELECT Hw3 FROM RAW_SCORES)/hw3M);
SET FExamPct = ((SELECT FExam FROM RAW_SCORES)/FExamM);
SET WeightedAverage = ((HW1Pct * hw1w) + (HW2Pct * hw2aw) + (HW2bPct * hw2bw) + (MidtermPct * Midtermw) + (HW3Pct * hw3w) + (FExamPct * FExamw));
SELECT SSN, FName, LName, HW1Pct, HW2Pct, MidtermPct, HW3Pct, FExamPct FROM RAW_SCORES; 回答 1
Stack Overflow用户
发布于 2017-03-10 06:26:47
这样想--
所有学生
SELECT *
FROM RAW_SCORES
WHERE SSN NOT IN ('0002','0001')HW1的平均得分
SELECT AGV(HW1)
FROM RAW_SCORES
WHERE SSN NOT IN ('0002','0001')所有学生的平均百分比
SELECT SSN, HW1 / X.AVG_HW1 AS PERCENT_OF_AVG_HW1
FROM RAW_SCORES
CROSS JOIN (
SELECT AGV(HW1) AS AVG_HW1
FROM RAW_SCORES
WHERE SSN NOT IN ('0002','0001')
) AS X
WHERE SSN NOT IN ('0002','0001')等
我知道这不是你的教授所要求的--我的观点不是为你做功课，而是帮助你理解如何在
中使用集合来解决这个问题，而不是像你在编写一种面向控制的语言一样。因此，我以这种方式给出了一个表达式的示例，并假设您可以将其扩展到所需的表达式。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42706525
复制相关文章
相似问题
腾讯云开发者
