我如何编写SQL查询从两个表(与Nodejs)获得以下输出

Question

表1:老师

+------+--------------+
| id   | Teacher_name |
+------+--------------+
| 1    | M       |
| 2    | S        |
+------+--------------+

表2:学生

+------+--------------+
| id   | Subject_name |
+------+--------------+
|    1 | English      |
|    1 | Marathi      |
|    1 | Hindi        |
|    2 | Biology      |
|    2 | Physics      |
|    2 | History      |
+------+--------------+

我需要像

这样的输出

[
{
"Teacher_name": "M",
"Subjects": [
{
"Subject_name": "english"
},
{
"Subject_name": "marathi"
},
{
"Subject_name": "hindi"
}
]
},
{
"Teacher_name": "S",
"Subjects": [
{
"Subject_name": "biology"
},
{
"Subject_name": "physics"
},
{
"Subject_name": "history"
}
]
}
]

Answer 1

sql表中不能有重复的ID。例如，students表有多个id为1和多个id为2。你可以有一个标签为"id"的列虽然它实际上不是一个ID，但这相当于贴错了标签，让任何看过表格的人都感到困惑。这些表的实际结构是什么?为了在这些表之间创建关系，您需要学生表中的一列引用教师，因为Teachers and Students之间分别存在One to Many关系。例如，"一个老师可以有很多学生。">

+------+--------------+
| id   | Subject_name | Teacher
+------+--------------+
|    1 | English      |    1
|    2 | Marathi      |    1
|    3 | Hindi        |    1
|    4 | Biology      |    2
|    5 | Physics      |    2
|    6 | History      |    2
+------+--------------+

那么你会使用"内连接"类似于:

SELECT Subject_name, Teacher_name FROM Students INNER JOIN teacher ON (teacher.id = Students.teacher);

这将返回:

+--------------+
| Subject_name | Teacher
+--------------+
| English      |    M
| Marathi      |    M
| Hindi        |    M
| Biology      |    S
| Physics      |    S
| History      |    S
+--------------+

此外，您需要的输出将需要消化该查询的输出，然后用该信息构建一个对象或数组。此外，在任何表中都没有"subject_name"的字段。因此，除非您有备用数据源或另一个表，否则该字段基本上必须手动填充。

教师与受试者之间似乎也存在一种one to many关系，即……"一个老师可以教很多科目"。因此，您可能需要这样一个表。

调用表格"subjects">

Teacher_name  |  Subject_name
M        |    english
M        |    marathi
M        |    hindi
S        |    biology
S        |    physics
S        |    history

查询将简单地为select * from Subjects;

或者如果您希望记录是唯一可识别的。

ID  |  Subject_name  | Teacher
1   |    english     |    1
2   |    marathi     |    1
3   |    hindi       |    1
4   |    biology     |    2
5   |    physics     |    2
6   |    history     |    2

那么您将使用类似的内部连接，如:

SELECT Teacher_name, Subject_name FROM Subjects INNER JOIN teacher ON (teacher.id = Subjects.teacher);

将产生以下结果:

Subject_name  | Teacher
english     |    M
marathi     |    M
hindi       |    M
biology     |    S
physics     |    S
history     |    S

node.js部分只是创建连接并为其提供正确的sql字符串的问题。一个例子:https://www.w3schools.com/nodejs/nodejs_mysql_select.asp

var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "yourusername",
password: "yourpassword",
database: "mydb"
});
con.connect(function(err) {
if (err) throw err;
con.query("SELECT * FROM customers", function (err, result, fields) {
if (err) throw err;
console.log(result);
});
});

这完全取决于你的表结构。