mysql - MySQL 查询构造问题

Question

我正在尝试在 MySQL 中编写一个查询来执行以下操作:

“如果该系中没有学生没有指定的顾问，请查找现有的指导者最多，并将其中一名指导者重新分配给新的指导者导师。”

表定义是:

Advisor(i_id, s_id) - i_id, s_id is the primary key.
Instructor(i_id, name, dept_name, salary) - i-id is the primary key.
Student(s_id, name, dept_name, tot_cred) - s_id is the primary key.

我编写了以下查询，该查询不完整，但我不确定如何继续:

SELECT MAX(counted) 
(SELECT i_id, COUNT(s_id) AS counted
 FROM Advisor
 GROUP BY i_id) ;

任何帮助将不胜感激!!

Answer 1

该系的学生没有指定的顾问:

SELECT Student.s_id from Student
WHERE s_id NOT IN ((SELECT DISTINCT(s_id) from Advisor) AS Assigned)

查找获得最多建议的现有讲师

SELECT MAX(value), i_id
FROM
(SELECT i_id, COUNT(*) as value
FROM Advisor
GROUP BY i_id) AS counted) 

将他们的一名顾问重新分配给新讲师

Done Manually

尚未测试过，这里可能存在一些语法错误