我需要创建一个半复杂的存储过程。我在这里不是一个真正的DBA,但在我的公司里没有人比我更好。基本的想法是我有一组任务。用户完成这些任务并为每个完成的任务获取一个标志。要被视为“完成”,用户必须完成一组给定任务中的所有任务。关键在于,其中一些任务是其他任务组的包装。
例如,我们有以下可能的任务:
Task 1
Task 2
Task 3
Task 4 -> (1, 2, 3)
Task 5 -> (1, 2)
Task 6
Task 7 -> (5, 6)
一旦用户完成了任务1和2,他们就隐式地完成了任务5。一旦他们完成任务3,他们将隐式地完成任务4。
如果另一个用户完成了任务1、2和6,他们将隐式地完成1、2、5、6、7。
相反,如果一个任务只需要任务7,那么它将扩展为需要任务6和5,这将需要任务1和任务2。
我有五个基本表:usercompletedtask、assignmentrequiredtask、compoundtask、usertasksassignment和task。我省略了user和assignment表,因为它是多余的。
userCompletedTask:
userID (Foreign Key on user)
taskID (Foreign Key on task)
usertTaskAssignment:
userID (Foreign key on user)
assignmentID (Foreign key on assignment)
assignmentRequiredTask:
assignmentID (Foreign key on assignment)
taskID (Foreign key on task)
task:
taskID (primary key)
compound (boolean flag. If 1, it is compound)
compoundTask:
parentID (foreign key on task)
childID (foreign key on task)
为用户分配了一个需要完成任务4的usertaskassignment。我想构建一个存储过程,它将根据assignmentrequiredtasks检查usercompletedtasks,检查是否有合适的compoundtasks。
伪代码如下:
collection tasksCompleted = user->getTasksCompleted
collection tasksRequired = new collection
foreach task in assignment->getRequiredTasks
if(task.isCompound)
tasksRequired->addAll(getCompountTasks(task))
else
tasksRequired->add(task)
if tasksCompleted->containsAll(tasksRequired)
return true
else
return false
我只是不太了解mysql/sql的内部结构,无法将其转换为存储过程。最后一种方法是将代码拉入应用程序,但这确实适合数据级别。任何帮助都将不胜感激。
编辑
正如下面指出的,compoundtask条目本身可以是复合任务。因此,您需要一个递归钻取来获得需要完成的所有非复合任务的列表。我扩展了上面的例子来说明这一点。
最佳答案
实际上,您的compoundTask
表正在复制您的task
表。如果您只需向任务表中添加一个可为空的parent_id
列,就可以在一个表中获得相同的结果。
在这里,这是未经测试的,甚至可能不是有效的mysql,但它应该让您开始:
SELECT DISTINCT taskID FROM task AS t LEFT JOIN compoundTask AS ct ON ct.taskID = t.taskID
INNER JOIN userCompletedTask AS uct ON uct.taskID = t.taskID
INNER JOIN userCompletedTask AS uctCompound ON uctCompound.taskID = ct.taskID
WHERE uct.userID = @user AND uctCompound.userID = @user
由此返回的任何
taskID
s都将为指定的@user
完成。我已经很久没有做过mysql了,所以这可能根本不起作用。此外,如果您可以合并
compoundTask
和task
表,则第二个INNER JOIN
表将是不必要的。