自动投放

Question

我有课程表：

ID      Lesson    date        time1   time2    Teacher
-------------------------------------------------------
M001    Math     01-08-2017   17:00   19:00
M002    English  01-08-2017   13:00   15:00
M003    Design   01-08-2017   17:00   19:00
M001    Math     02-08-2017   13:00   15:00
M002    English  02-08-2017   17:00   19:00
M003    Design   02-08-2017   17:00   19:00

我也有带优先级的教师桌

ID      Name     Lesson    Priority
-----------------------------------
001     John     Math         1
002     Mike     Math         2
003     Clara    Math         3
004     Noah     Design       1
005     Jack     Design       2
006     Kath     English      1
007     Steve    English      2

优先级决定了谁将在时间表上授课，但如果时间表已经安排了老师，那么第二位老师就会被列入时间表，依此类推。我仍然不知道如何使它work..please帮助..

谢谢..。

Answer 1

您想尝试使用存储过程插入

可能看起来像这样

CREATE STORED_PROCEDURE SP_INSERT_TEACHER

@TeachID int = 0,
@TeachName nvarchar(max), 
@TeachLesson nvarchar(max), 
@TeachPriority int

AS
BEGIN

SET NOCOUNT ON;

IF dbo.Teacher.Lesson = @TeachLesson AND dbo.Teacher.Priority = @TeachPriority
@TeachPriority + 1



INSERT INTO dbo.Teacher(ID, Name, Lesson, Priority) 
values(@TeachID, @TeachName, @TeachLesson, @TeachPriority)

END

这基本上是检查现有记录，如果一条记录已经与课程匹配，则按优先级将插入的教师返回1

只需确保始终通过SP插入