MySQL连接逗号分隔字段 [英] MySQL Join Comma Separated Field

问题描述

我有两个桌子.第一张表是batch表，其中包含用逗号分隔的批次"字段中的学生ID

I have two tables. First Table is a batch table that contain comma separated student id in field "batch"

batch
--------------
id      batch 
--------------
1       1,2     
2       3,4

第二个表是标记

marks
----------------------
id  studentid   subject     marks
1     1         English     50
2     2         English     40
3     3         English     70
4     1         Math        65
5     4         English     66
6     5         English     75
7     2         Math        55

我们如何查找那些第一批id = 1的学生在不使用子查询的情况下英语得分超过45分的人.

How we can find those students of first batch id =1 who have scored more than 45 marks in English without using sub query.

我发现仅通过一个查询即可完成此操作的问题是，我们不能在JOIN语句中将 IN 用作关联运算符

Problem i found to get this done using a single query is that we can not use IN as an association operator in JOIN statement

下面的查询需要进行哪些更改才能使其正常工作?

What changes are required in below query to make it work?

SELECT * FROM batch
INNER JOIN marks ON marks.studentid IN(batch.batch) where batch.id = 1

推荐答案

SELECT  m.studentId
FROM    batch b
JOIN    marks m
ON      FIND_IN_SET(m.studentId, b.batch)
        AND m.subject = 'English'
GROUP BY
        m.studentId
HAVING  SUM(marks) > 45

这篇关于MySQL连接逗号分隔字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！