Mysql IN function

Question

class_table

+----+-------+--------------+
| id |teac_id| student_id   |
+----+-------+--------------+
| 1  | 1     | 1,2,3,4      |
+----+-------+--------------+

student_mark

+----+----------+--------+
| id |student_id| marks  |
+----+----------+--------+
| 1  | 1        | 12     |
+----+----------+--------+
| 2  | 2        | 80     |
+----+----------+--------+
| 3  | 3        | 20     |
+----+----------+--------+

我有这两个表格，我想计算学生的总分，我的sql是：

SELECT SUM(`marks`) 
FROM `student_mark`
WHERE `student_id` IN 
(SELECT `student_id` FROM `class_table` WHERE `teac_id` = '1')

但这会返回null，请帮忙！！

数据库小提琴

Answer 1

• 首先，切勿在列中存储逗号分隔的数据。您应该真正规范化您的数据。所以基本上，你可以有一个多对多表映射teacher_to_student，它将有teac_id和student_id列。
• 在这种特殊情况下，您可以使用Find_in_set()功能。

从您当前的查询来看，您似乎正在尝试获得教师的总分(汇总他/她所有学生的分数(。

尝试：

SELECT SUM(sm.`marks`) 
FROM `student_mark` AS sm
JOIN `class_table` AS ct 
ON FIND_IN_SET(sm.`student_id`, ct.`student_id`) > 0
WHERE ct.`teac_id` = '1' 

如果您想获得每个学生的总分，则需要添加一个Group By。查询如下所示：

SELECT sm.`student_id`, 
SUM(sm.`marks`) 
FROM `student_mark` AS sm
JOIN `class_table` AS ct 
ON FIND_IN_SET(sm.`student_id`, ct.`student_id`) > 0
WHERE ct.`teac_id` = '1' 
GROUP BY sm.`student_id`

Answer 2

以防万一你想知道为什么，它返回 null 的原因是子查询作为一个整体返回为"1,2,3,4"。您需要的是使其分别返回 1,2,3,4。

查询返回的内容

SELECT SUM(`marks`) 
FROM `student_mark`
WHERE `student_id` IN ('1,2,3,4')

你所期望的是

SELECT SUM(`marks`) 
FROM `student_mark`
WHERE `student_id` IN (1,2,3,4)

最好的方法是像@madhur所说的那样正常化。在您的情况下，您需要将老师和学生设置为一对多链接

+----+-------+--------------+
| id |teac_id| student_id   |
+----+-------+--------------+
| 1  | 1     | 1            |
+----+-------+--------------+
| 2  | 1     | 2            |
+----+-------+--------------+
| 3  | 1     | 3            |
+----+-------+--------------+
| 4  | 1     | 4            |
+----+-------+--------------+

Answer 3

如果您想根据带有 ID 的逗号分隔列表过滤表格，我的方法是 在列表的开头和结尾以及 ID 的开头和结尾附加额外的逗号，例如。1变得,1,，列表将变得,1,2,3,4,。这样做的原因是为了避免模棱两可的匹配，例如列表中的 1 匹配项 21 或 12。

此外，EXISTS非常适合这种情况，它与INSTR函数一起应该可以工作：

SELECT SUM(`marks`) 
FROM `student_mark` sm
WHERE EXISTS(SELECT 1 FROM `class_table` 
WHERE `teac_id` = '1' AND
INSTR(CONCAT(',', student_id, ','), CONCAT(',', sm.student_id, ',')) > 0)

演示

但是您不应该将相关 ID 作为逗号分隔的列表存储在一个单元格中 - 它应该是外键列以形成正确的关系。那时加入将变得微不足道。