update a set GroupType=b.GroupType from UserRole_Group a,UserRole_Group b,UsersInRole c,UsersInRole d where a.RoleGroupID=c.RoleGroupID and b.RoleGroupID=d.RoleGroupID and c.UserID=@UserID4 and d.UserID=@UserID1 and a.GroupType<b.GroupType
update a set GroupType=b.GroupType from UserRole_Group a,UserRole_Group b,UsersInRole c,UsersInRole d where a.RoleGroupID=c.RoleGroupID and b.RoleGroupID=d.RoleGroupID and c.UserID=@UserID4 and d.UserID=@UserID1 and a.GroupType<b.GroupType--删除UserRole_Group delete a from UserRole_Group a,UsersInRole b where a.RoleGroupID=b.RoleGroupID and b.UserID=@UserID1--删除UsersInRole delete UsersInRole where UserID=@UserID1
set GroupType=b.GroupType
from UserRole_Group a,UserRole_Group b,UsersInRole c,UsersInRole d
where a.RoleGroupID=c.RoleGroupID
and b.RoleGroupID=d.RoleGroupID
and c.UserID=@UserID4
and d.UserID=@UserID1
and a.GroupType<b.GroupType
可能我还没有说明白:
1.通过UsersInRole中的UserID找到这张表中对应的RoleGroupID
2.再用这个RoleGroupID找到UserRole_Group中的和之对应的的RoleGroupID(在这张表中RoleGroupID是标识列)
3.比较UserRole_Group表中的GroupType(INT类型 就是权限的大小 谁大就用谁)
4.然后把这个表中要比较的用户的权限都改成这个大的
5.这个大的用户的权限有个RoleGroupID,然后把它返回到UsersInRole中,把UsersInRole中对应的RoleGroupID修改成UserRole_Group的对应的
6.最后删除原来用户
总结起来要修改的是RoleGroupID,GroupType
set GroupType=b.GroupType
from UserRole_Group a,UserRole_Group b,UsersInRole c,UsersInRole d
where a.RoleGroupID=c.RoleGroupID
and b.RoleGroupID=d.RoleGroupID
and c.UserID=@UserID4
and d.UserID=@UserID1
and a.GroupType<b.GroupType--删除UserRole_Group
delete a
from UserRole_Group a,UsersInRole b
where a.RoleGroupID=b.RoleGroupID
and b.UserID=@UserID1--删除UsersInRole
delete UsersInRole where UserID=@UserID1
谢谢
刚才没有理解过来
不过我还是不明白为什么没有用到max就实现了?