select ForumName, Nick, Introduce
from Admin,BBSAdmin,Forum
where Admin.AdminID = BBSAdmin.AdminID and BBSAdmin.ForumID = Forum.ForumID如果要在同一行显示多人,则需要游标或临时表等中间处理过程才能达到
from Admin,BBSAdmin,Forum
where Admin.AdminID = BBSAdmin.AdminID and BBSAdmin.ForumID = Forum.ForumID如果要在同一行显示多人,则需要游标或临时表等中间处理过程才能达到
select Nick from Admin,BBSAdmin,Forum
where Admin.AdminID = BBSAdmin.AdminID and BBSAdmin.ForumID = Forum.ForumID and BBSAdmin.ForumID = @forumid
open c
fetch c into @name
while (@@sqlstatus=0)
begin
select @tname = @tname +','+ @name
endselect ForumName, @tname, Introduce
from Forum
where BBSAdmin.ForumID = @forumid
while (@@sqlstatus=0)
begin
select @tname = @tname +','+ @name
fetch c into @name
end
BBSAdmin: AdminID 管理员编号 ForumID BBS板块编号
Forum: ForumID BBS板块编号ForumName 讨论区名称 Introduce 讨论区介绍
请教如何按以下格式显示
ForumName Nick(多人) Introduce
select a.forumname,a.nick,a.introduce
from forum a,bbsadmin b admin c
where a.forumid=b.forumid
and b.adminid=c.adminid
declare @Tsql varchar(2200)
declare @Tmpmax int
declare @i int
select @Tmpmax=(select max(b.num) from (select count(*) as num from #bbsadmin group by forumid) b)
set @i=1
set @Tsql='select forumname,introduce,'
while @i<=@TmpMax
begin
set @Tsql=@Tsql+'(select a.Nick from #admin a,(select adminid,forumid,(select count(adminid) from #bbsadmin where forumid=e.forumid and adminid<=e.adminid) as adminlevel from #bbsadmin e) b where a.adminid=b.adminid and b.forumid=c.forumid and b.adminlevel='+convert(varchar(10),@i)+') as 管理员'+convert(varchar(10),@i)+','
set @i=@i+1
end
set @Tsql=left(@Tsql,len(@Tsql)-1)+' from #forum c'
exec(@Tsql)
create table #admin
(
adminid int,
Nick varchar(20)
)
go
create table #bbsadmin
(adminid int,
forumid int
)
go
create table #forum
(
forumid int,
forumname varchar(20),
introduce text
)
go
csdn wylyf wyly wyl lyf 李寻欢
vchelp lyf 李寻欢 NULL NULL NULL
263 wylyf NULL NULL NULL NULL
chinaren wyly NULL NULL NULL NULL