描述见这帖 http://topic.csdn.net/u/20110306/16/f2903a05-39d5-4e99-b6f4-7cf33ed82fd6.html因为是写在C#中的SQL语句,我根据各位高手给的提示自己写了一句String strsql="Update Dormitory Set SNo=S.SNo from Dormitory D inner join Student S on D.StuSex=S.StuSex Where D.SNo is null and S.SGrade='" + grade + "'and S.SClass='" +stclass + "'and LNo ='"+lno+"'"可是运行后发现,更新到Dormitory中的SNo的只是从Student表中选取的符合条件的第一行的SNo值,而且是重复出现,请各位高手帮我看看要怎么改才能将从Student表中选出的多个SNo不重复的更新到Dormitory表,且当Dormitory表中可供分配的宿舍分配完后(即满足条件的Dormitory.SNo全部被Student.SNo更新到),或者待分配的Student.SNo都已经分配好时不管Dormitory.SNo是不是都已经更新到都结束分配操作。以上,麻烦各位高手可以帮小菜鸟我看看,或者用触发器的话怎么解决这些问题?因为要赶在这星期前解决这个问题,要是能解决的话,万分感谢啊!
问题1:一个宿舍几个人?
问题2:以什么顺序分配人住进各个宿舍,除了性别之外?
cr eate table Student(SNo varchar(10),SGrade nvarchar(10),SClass nvarchar(10),SSex nvarchar(10))
insert into Student select '001','2005','高三(1)班','男'
insert into Student select '002','2005','高三(1)班','男'
insert into Student select '003','2005','高三(2)班','男'
insert into Student select '004','2006','高二(1)班','女'
insert into Student select '005','2007','高一(2)班','男'
insert into Student select '006','2007','高一(1)班','女'
create table Dormitory(LNo nvarchar(10),RNo varchar(10),RBNo varchar(10),RSex nvarchar(10),SNo varchar(10))
insert into Dormitory select '1号','101','A','男','001'
insert into Dormitory select '1号','101','B','男',null
insert into Dormitory select '1号','102','A','男',null
insert into Dormitory select '1号','102','B','男',null
insert into Dormitory select '2号','201','A','女',null
insert into Dormitory select '2号','201','B','女','004'
insert into Dormitory select '2号','202','A','女',null
insert into Dormitory select '2号','202','B','女',null
go
;with c1 as(
select ROW_NUMBER() over(PARTITION by SSex order by sno)rn,sno,ssex from Student a where not exists(select 1 from Dormitory where SNo=a.SNo)
),c2 as(
select ROW_NUMBER() over(PARTITION by RSex order by Lno,Rno,RBNo)rn,* from Dormitory where SNo is null
),c3 as(
select a.LNo,a.RNo,a.RBNo,a.RSex,b.sno from c2 a inner join c1 b on a.RSex=b.SSex and a.rn=b.rn
)update Dormitory set SNo=c3.sno from Dormitory t inner join c3 on t.LNo=c3.LNo and t.RBNo=c3.RBNo and t.RNo=c3.RNo and t.RSex=c3.RSex
select * from Dormitory
go
drop table student,dormitory
/*
LNo RNo RBNo RSex SNo
---------- ---------- ---------- ---------- ----------
1号 101 A 男 001
1号 101 B 男 002
1号 102 A 男 003
1号 102 B 男 005
2号 201 A 女 006
2号 201 B 女 004
2号 202 A 女 NULL
2号 202 B 女 NULL(8 行受影响)*/
--参考
create proc pr_name(@stclass varchar(20),@grade varchar(20),@lno varchar(20))
as
begin
declare @sno varchar(10),@sex char(5)
Declare MyCusror Cursor For Select SNo,SSex From Student where SClass=@stclass and SGrade=@grade
Open MyCursor
Fetch next From MyCursor Into @sno,@sex
While(@@Fetch_Status = 0)
begin
if exists(select 1 from Dormitory where LNo=@lno and SNo is null and RSex=@sex)
begin
update Dormitory set SNo=@sno
where LNo+RNo+RBNo+RSex=(select top 1 LNo+RNo+RBNo+RSex --宿舍没主键难处理
from Dormitory where LNo=@lno and SNo is null and RSex=@sex)
end
Fetch next From MyCursor Into @sno,@sex
end
Close MyCursor
Deallocate MyCursor
end