create proc p1 (@sex char(2), @BedchamberType int, @DepartType int) select top 1 B.sFlatName, A.sFloorID, A.sBedChamberID from BedChambers as A left join Flat as B on A.sFlatID = B.sFlatID left join Floor as C on A.sFloorId = C.sFloorId and A.sFlatID = C.sFlatID where A.iBedChamberNumber > A.iAssign and B.sSex = @sex and C.sFlatType1 = @DepartType or C.sFlatType2 = @DepartType and A.sTypeId = @BedchamberType go
修改: create proc p1 (@sex char(2), @BedchamberType int, @DepartType int) select top 1 B.sFlatName, A.sFloorID, A.sBedChamberID from BedChambers as A left join Flat as B on A.sFlatID = B.sFlatID left join Floor as C on A.sFloorId = C.sFloorId and A.sFlatID = C.sFlatID left join BedchamberType as D on A.sTypeId = D.sTypeId where A.iBedChamberNumber > A.iAssign and B.sSex = @sex and C.sFlatType1 = @DepartType or C.sFlatType2 = @DepartType and D.sTypeName = @BedchamberType go 人数由: A.iBedChamberNumber > A.iAssign 控制。
是不是在操作(update..) iAssign时(加一减一) 要加锁啊begin tran update Bedchambers set iassign=iassign+1 WHERE sFlatId='01' and sFloorId='01' and sBedChamberId='101' waitfor delay '00:00:30' commit tran 等待时间多少为好。
是不是在操作(update..) iAssign时(加一减一) 要加锁啊begin tran update Bedchambers set iassign=iassign+1 WHERE sFlatId='01' and sFloorId='01' and sBedChamberId='101' waitfor delay '00:00:30' commit tran 还有等待时间多少为好呢!
select top 1 B.sFlatName, A.sFloorID, A.sBedChamberID from BedChambers as A
left join Flat as B on A.sFlatID = B.sFlatID
left join Floor as C on A.sFloorId = C.sFloorId and A.sFlatID = C.sFlatID
where A.iBedChamberNumber > A.iAssign
and B.sSex = @sex
and C.sFlatType1 = @DepartType or C.sFlatType2 = @DepartType
and A.sTypeId = @BedchamberType
go
“and A.sTypeId = @BedchamberType”
sTypeId是类别编号,bedchambertype是类别名称,只差一步,是否还要有一个关联?
而且会不会出现8个人分9个人的情况?
create proc p1 (@sex char(2), @BedchamberType int, @DepartType int)
select top 1 B.sFlatName, A.sFloorID, A.sBedChamberID from BedChambers as A
left join Flat as B on A.sFlatID = B.sFlatID
left join Floor as C on A.sFloorId = C.sFloorId and A.sFlatID = C.sFlatID
left join BedchamberType as D on A.sTypeId = D.sTypeId
where A.iBedChamberNumber > A.iAssign
and B.sSex = @sex
and C.sFlatType1 = @DepartType or C.sFlatType2 = @DepartType
and D.sTypeName = @BedchamberType
go
人数由:
A.iBedChamberNumber > A.iAssign 控制。
第一问题已解决,
如果是多机操作会不会有问题,像什么并发控制,加锁等。
要加锁啊begin tran
update Bedchambers set iassign=iassign+1 WHERE sFlatId='01' and sFloorId='01' and sBedChamberId='101'
waitfor delay '00:00:30'
commit tran
等待时间多少为好。
要加锁啊begin tran
update Bedchambers set iassign=iassign+1 WHERE sFlatId='01' and sFloorId='01' and sBedChamberId='101'
waitfor delay '00:00:30'
commit tran
还有等待时间多少为好呢!