gpt4 book ai didi

where子句内和之间的mysql等式

转载 作者:行者123 更新时间:2023-11-29 05:17:09 25 4
gpt4 key购买 nike

我需要计算一些数学方程并检查它是否在另一个表中存储的范围内。如下表所示:

+--------------+-------+--------+-------+--------+
| no | TOTAL | Out Of | grad | point |
+--------------+-------+--------+-------+--------+
| 1000 | 833 | 1100 | A | 12 |
+--------------+-------+--------+-------+--------+

av 尝试通过以下方式实现它

查询

SELECT no,sum(score) as TOTAL,(100*count(scores.code)) as "Out Of",
grad, point
from scores,gradings
where (no=1000) and (exam=1)
and ((sum(score)/(100*count(exams_scores.subject_code))*100)
between gradings.range_begin and gradings.range_end)
;

分级表

+---------------------------------------------------+
| id | range_begin | range_end | grad | point |
+---------------------------------------------------+
| 1 | 70 | 80 | A | 12 |
+---------------------------------------------------+
| 2 | 60 | 70 | B | 11 |
+---------------------------------------------------+

分数表

+---------------------------------------+
| no | exam | code | score |
+---------------------------------------+
|1000 | 1 | 121 | 70 |
+---------------------------------------+
|1000 | 1 | 231 | 80 |
+---------------------------------------+
|1001 | 1 | 121 | 56 |
+---------------------------------------+
|1001 | 1 | 231 | 85 |
+---------------------------------------+

我得到了

错误信息

Mysql ERROR 1111 (HY000): Invalid use of group function

我认为错误来自带有 between 子句的等式,但我不知道如何解决它..

有人能帮忙吗?

最佳答案

首先,将每个学生的考试成绩相加得到总数:

SELECT `no`, SUM(score) AS total, 100*COUNT(`code`) AS outof
FROM scores
WHERE exam=1
GROUP BY `no`

然后,将该查询的结果加入到评分中:

SELECT s.`no`, s.total, s.outof, g.grad, g.point
FROM (
SELECT `no`, SUM(score) AS total, 100*COUNT(`code`) AS outof
FROM scores
WHERE exam=1
GROUP BY `no`
) s
JOIN gradings g ON s.total/s.outof*100 BETWEEN g.range_begin AND g.range_end
WHERE s.`no`=1000;

关于where子句内和之间的mysql等式,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/31231135/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com