我在Nodejs和MySQL后端(React前端(遇到了一个数组问题。前端发送一个带有两个对象的请求,它的主体是API,然后使用这两个对象作为条件对表1执行SELECT查询。这个查询的结果是一个对象数组,如下所示:
[
{
"userID": 245
},
{
"userID": 244
},
{
"userID": 247
}
]
然后,我对表2执行第二个SELECT查询,将第一个查询中的userID作为条件(表2中的每个userID都包含"firstName"one_answers"lastName"属性(。我试图实现的目标如下,我可以将其发送回前端:
[
{
userID: 245,
firstName: "Test",
lastName: "Student1",
},
{
userID: 244,
firstName: "Test",
lastName: "Student2",
},
{
userID: 247,
firstName: "Test",
lastName: "Student3",
},
];
以下是我要实现的功能:
export const view = async (req, res) => {
const { classIdentifier, syllabusCode } = req.body;
// User the connection
db.query(
"SELECT DISTINCT studentID AS 'userID' FROM classIndex WHERE teacherID = ? AND classIdentifier = ? AND syllabusCode = ?;",
[req.params.teacherID, classIdentifier, syllabusCode],
(err, rows) => {
const allData = [];
const final = async () => {
await rows.forEach((row) => {
db.query(
"SELECT userID, firstName, lastName from userIndex WHERE userID = ?",
[row.userID],
(err, data) => {
if (!err) {
data.forEach((users) => {
allData.push({
userID: users.userID,
firsName: users.firstName,
lastName: users.lastName,
});
});
} else {
console.log(err);
}
}
);
});
};
final().then((result) => {
console.log(result);
});
console.log(allData);
}
);
};
请让我知道,如果我能提供更多的代码,任何帮助都将不胜感激!
您可以使用嵌套查询。现在你运行
SELECT DISTINCT studentID AS 'userID' FROM classIndex WHERE teacherID = ? AND
classIdentifier = ? AND syllabusCode = ?;
然后对每个条目进行第二次查询。
您可以将查询组合起来,让mysql为您完成这项工作。(我只是不确定Distinct是否必须在嵌套查询中出现。(
SELECT firstName, lastName, userID FROM userIndex WHERE userID LIKE (
SELECT DISTINCT studentID
FROM classIndex
WHERE teacherID = ? AND classIdentifier = ? AND syllabusCode = ?
)
子查询的简短信息