mysql - 使用 Intersect 和电子邮件格式的多个输入值时出现 ER_PARSE_ERROR

Question

我正在尝试使用 INTERSECT 子句构建 SQL 查询。我得到了一系列电子邮件 ID，作为返回，我需要给出输入中的电子邮件 ID 提供的所有教师之间的共同学生。

表的结构是:

id | teacher_email    | student_email       | valid |
41 | Tom@gmail.com    | May@gmail.com       |     1 |
42 | Tom@gmail.com    | jerry@gmail.com     |     1 |
43 | Tom@gmail.com    | Kestov@gmail.com    |     1 |
44 | Toni@gmail.com   | hdjh@kd.com         |     1 |
58 | larry@gmail.com  | jerry@gmail.com     |     1 |
59 | gerrad@gmail.com | jerry@gmail.com     |     1 |
60 | gerrad@gmail.com | Kestov@gmail.com    |     1 |
61 | gerrad@gmail.com | Katrin@gmail.com    |     1 |
62 | gerrad@gmail.com | Piniyara@gniail.com |     1 |
63 | gerrad@gmail.com | Ritz@gmail.com      |     1 |
64 | gerrad@gmail.com | Taz@gmail.com       |     1 |
65 | gerrad@gmail.com | Fensuk@gmail.com    |     1 |
66 | gerrad@gmail.com | Joe@gmail.com       |     1 |
67 | gerrad@gmail.com | Mustafa@gmail.com   |     1 |
69 | Tom@gmail.com    | Fensuk@gmail.com    |     1 |
70 | Tom@gmail.com    | Taz@gmail.com       |     1 |

这是API代码

router.get("/api/commonstudents", (req, res) => {
console.log("Trying to retrieve common students of given teachers...")
console.log("Teacher's email IDs: " + req.query.teacher)

const teacherEmailIDs = req.query.teacher
var commonStudentsForTeachersQueryString = "";
const connection = getConnection()

console.log("no of teachers: " + teacherEmailIDs)

teacherEmailIDs.forEach(function(teacherEmailID) {

    console.log(teacherEmailID);
    commonStudentsForTeachersQueryString += "select student_email from register where teacher_email=? and valid=1";
    commonStudentsForTeachersQueryString += " INTERSECT "

})

commonStudentsForTeachersQueryString = commonStudentsForTeachersQueryString.substr(0, commonStudentsForTeachersQueryString.lastIndexOf(" INTERSECT "))

console.log("Query formed is :: " + commonStudentsForTeachersQueryString);

connection.query(commonStudentsForTeachersQueryString, teacherEmailIDs, (errCommonStudentTeacher, rowsCommonStudentTeacher) => {
if (errCommonStudentTeacher) {
  console.log("Failed to query for users: " + errCommonStudentTeacher)
  console.error(errCommonStudentTeacher);
  res.status(500).json({"message": "Some Internal Error Occured"})
  return
}
if (rowsCommonStudentTeacher === undefined || rowsCommonStudentTeacher.length == 0) {
  //rows empty or does not exist
  console.log("No common students")
  res.status(404).json({"message": "No Common Students found"})
}
else{
  //common students found
  console.log("Common students Found" + rowsCommonStudentTeacher.length)

  const commonStudents = rowsCommonStudentTeacher.map((commonStudent)=>{
    return commonStudent.student_email
  })

  res.status(200).json({"students": commonStudents})
}
})
})

这是控制台打印的内容:

Harshs-MacBook-Air:nodejs_api harshvardhan$ node app.js
Server is up and listening on 3003...
Trying to retrieve common students of given teachers...
Teacher's email IDs: Tom@gmail.com,gerrad@gmail.com,larry@gmail.com
no of teachers: Tom@gmail.com,gerrad@gmail.com,larry@gmail.com
Tom@gmail.com
gerrad@gmail.com
larry@gmail.com
Query formed is :: select student_email from register where teacher_email=? and valid=1 INTERSECT select student_email from register where teacher_email=? and valid=1 INTERSECT select student_email from register where teacher_email=? and valid=1
Failed to query for users: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT select student_email from register where teacher_email='gerrad@gmail.c' at line 1
{ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT select student_email from register where teacher_email='gerrad@gmail.c' at line 1
at Query.Sequence._packetToError (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
at Protocol._parsePacket (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/Parser.js:433:10)
at Parser.write (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/Parser.js:43:10)
at Protocol.write (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket.<anonymous> (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/Connection.js:91:28)
at Socket.<anonymous> (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/Connection.js:525:10)
at emitOne (events.js:116:13)
at Socket.emit (events.js:211:7)
--------------------
at Pool.query (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/Pool.js:199:23)
at router.get (/Users/harshvardhan/Documents/work/test/nodejs_api/routes/admin.js:129:14)
at Layer.handle [as handle_request] (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/layer.js:95:5)
at next (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/route.js:137:13)
at Route.dispatch (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/route.js:112:3)
at Layer.handle [as handle_request] (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/layer.js:95:5)
at /Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/index.js:281:22
at Function.process_params (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/index.js:335:12)
at next (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/index.js:275:10)
at Function.handle (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/index.js:174:3)
 code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'INTERSECT select student_email from register where teacher_email=\'gerrad@gmail.c\' at line 1',
  sqlState: '42000',
  index: 0,
  sql: 'select student_email from register where teacher_email=\'Tom@gmail.com\' and valid=1 INTERSECT select student_email from register where teacher_email=\'gerrad@gmail.com\' and valid=1 INTERSECT select student_email from register where teacher_email=\'larry@gmail.com\' and valid=1' }
::ffff:127.0.0.1 - GET /api/commonstudents?teacher=Tom@gmail.com&teacher=gerrad@gmail.com&teacher=larry@gmail.com HTTP/1.1 500 41 - 65.808 ms

当我按原样使用时，该查询效果很好。

select student_email from register where teacher_email='Tom@gmail.com' and valid=1

但是当与其他查询一起使用 INTERSECT 时，会出现上述错误。

这个问题如何解决，我认为这与电子邮件格式及其字符编码有关。我浏览了论坛并尝试了一些方法才到达这里。感谢您为正确方法提供的任何指示。

Here是供引用的 GitHub 链接。

Answer 1

mysql 中没有 INTERSECT ..

如果你确实需要相交，你可以使用多个内部连接

select student_email 
from register r
INNER JOIN  (
  select student_email from 
  register where teacher_email=? and valid=1 
) t1 ON t1.student_email = r.student_email
INNER JOIN  (
  INTERSECT 
  INTERSECT select student_email from register 
  where teacher_email=? and valid=1
) t2 ON t2.student_email = t1.student_email 
where r.teacher_email=? and r.valid=1