表TableA,里面有2个IsTeacher(是否是教师),SubmitedBy(提交者),
TableA中的记录可以是教师提交,也可以是学生提交,
IsTeacher SubmitedBy
教师提交 1 TeacherID
学生提交 0 StudentID现在要列出TableA的所有信息,在”提交者“一列显示提交者的名字。所以我需要做个判断
我的思路:循环遍历每条记录,每次遍历条记录同时,判断”是否是教师提交“,从而改变 查询条件,
这是我的第一个方法create procedure PR_GetTableAList
@count int,@flagTeachersubmit int,@id int
as
begin
select @count = count(*) from TableA
end
while @count>0
begin
set @count = @count-1
select top 1 @flagTeachersubmit =isteacher,@id = id from TableA
where ID not in (select top @count ID from TableA order by ID desc ) order by ID desc
if(@flagTeachersubmit = 1)
begin
select * from TableA,Vteacher where VTeacher.ID= TableA.SubmitedBy and TableA.id = @id
end
else
begin
select * from TableA,Student where Student.id = TableA.SubmitedBy and TableA.id = @id
end
end
end
GO
报”@count“(红色标记)有错,改不出来啊……这是怎么回事?后来想起了游标,查询后改为create procedure PR_GetTableAList
as
begin
declare @id int,@IssubmittedbyTeacher int
declare pcurr cursor for
select id from TableA
open pcurr
fetch next from pcurr into @id
while (@@fetch_status = 0)
begin
select @IssubmittedbyTeacher=IsTeacher from TableA where id = @id
if(@IssubmittedbyTeacher = 0)
begin
select * from TableA,Stundet where Stundet.id = TableA.submittedby and TableA.id = @id
end
else
begin
select * from TableA,Vteacher where VTeacher.ID = TableA.submittedby and TableA.id = @id
end
print (@id)
fetch next from pcurr into @id
end
close pcurr
deallocate pcurr
end
执行没错,查出来的数据也是全部的,但不在一个表中,绑定数据源也就只显示一条了,我总不能在用个union吧?请问有什么发那个方法没?最好效率高点
TableA中的记录可以是教师提交,也可以是学生提交,
IsTeacher SubmitedBy
教师提交 1 TeacherID
学生提交 0 StudentID现在要列出TableA的所有信息,在”提交者“一列显示提交者的名字。所以我需要做个判断
我的思路:循环遍历每条记录,每次遍历条记录同时,判断”是否是教师提交“,从而改变 查询条件,
这是我的第一个方法create procedure PR_GetTableAList
@count int,@flagTeachersubmit int,@id int
as
begin
select @count = count(*) from TableA
end
while @count>0
begin
set @count = @count-1
select top 1 @flagTeachersubmit =isteacher,@id = id from TableA
where ID not in (select top @count ID from TableA order by ID desc ) order by ID desc
if(@flagTeachersubmit = 1)
begin
select * from TableA,Vteacher where VTeacher.ID= TableA.SubmitedBy and TableA.id = @id
end
else
begin
select * from TableA,Student where Student.id = TableA.SubmitedBy and TableA.id = @id
end
end
end
GO
报”@count“(红色标记)有错,改不出来啊……这是怎么回事?后来想起了游标,查询后改为create procedure PR_GetTableAList
as
begin
declare @id int,@IssubmittedbyTeacher int
declare pcurr cursor for
select id from TableA
open pcurr
fetch next from pcurr into @id
while (@@fetch_status = 0)
begin
select @IssubmittedbyTeacher=IsTeacher from TableA where id = @id
if(@IssubmittedbyTeacher = 0)
begin
select * from TableA,Stundet where Stundet.id = TableA.submittedby and TableA.id = @id
end
else
begin
select * from TableA,Vteacher where VTeacher.ID = TableA.submittedby and TableA.id = @id
end
print (@id)
fetch next from pcurr into @id
end
close pcurr
deallocate pcurr
end
执行没错,查出来的数据也是全部的,但不在一个表中,绑定数据源也就只显示一条了,我总不能在用个union吧?请问有什么发那个方法没?最好效率高点
IsTeacher int,
SubmitedBy int
)create table t_Teacher(
fid int,
fname varchar(20)
)
create table t_Student(
fid int,
fname varchar(20)
)insert into t_Teacher
select 1,'ta' union all
select 2,'tb' union all
select 3,'tc' union all
select 4,'td' insert into t_Student
select 1,'sa' union all
select 2,'sb' union all
select 3,'sc' union all
select 4,'sd'
insert into TableA
select 1,1 union all
select 0,1 union all
select 1,2 union all
select 0,3
select IsTeacher,SubmitedBy,case when t1.IsTeacher=1 then t2.fname
else t3.fname end SubmitedBy from TableA t1
left join t_Teacher t2 on t1.SubmitedBy=t2.fID and t1.IsTeacher=1
left join t_Student t3 on t1.SubmitedBy=t3.fID and t1.IsTeacher<>1drop table tablea
drop table t_Teacher
drop table t_StudentIsTeacher SubmitedBy SubmitedBy
1 1 ta
0 1 sa
1 2 tb
0 3 sc
left join student b
on a.submitedBy = b.studentid
and 1=case when isteacher=1 then 0 else 1 end
left join teacher c
on a.submitedBy = c.teacherid
and 1=case when isteacher=1 then 1 else 0 end如果isteacher非bool而是tinyint或是int,可能要选择更多的表,这时动态语句或者转换为xml操作就很方便了。
以前写过几个,如果有这个需要再写给你了。