下面是例表: 1表 Users:
UsersID UsersName 1 franky
2 fengwe 2表 Club:
ClubID ClubName 1 fengClub
2 frankClub 3表 ClubBBS: 注:版块
ClubBBSID ClubID
1 2
2 1 4表 Bbs: 注:版块内的帖子
BbsID BbsTitle UsersID 1 franky is man 2
2 I am a body 1 5表 BbsRep: 注:回复主题帖子
BbsRepID BbsID BbsRepTitle
1 1 Good!
2 1 Very Good!
3 2 No,man! 目的:通过Bbs表内的主题帖子,来显示UsersName,ClubName,统计出各主题帖子回复数,然后根据回复数排序! 帮帮我,用SQL不是很精!跪求——————————
UsersID UsersName 1 franky
2 fengwe 2表 Club:
ClubID ClubName 1 fengClub
2 frankClub 3表 ClubBBS: 注:版块
ClubBBSID ClubID
1 2
2 1 4表 Bbs: 注:版块内的帖子
BbsID BbsTitle UsersID 1 franky is man 2
2 I am a body 1 5表 BbsRep: 注:回复主题帖子
BbsRepID BbsID BbsRepTitle
1 1 Good!
2 1 Very Good!
3 2 No,man! 目的:通过Bbs表内的主题帖子,来显示UsersName,ClubName,统计出各主题帖子回复数,然后根据回复数排序! 帮帮我,用SQL不是很精!跪求——————————
解决方案 »
- 在其他页上删除登录时记录的cookie,怎么做呢
- 关于标题动态显示的问题
- ajax control toolkit里的CascadingDropDown 控件能不能控制它只响应用户对dropdownlist的操作
- 请教大家一个asp.net URL重写的问题? 急急急!!!!!
- 哪位大哥能给小弟提供 Regulator 这个工具, 谢谢
- 如何设置 ASP.NET用户权限?
- asp.net mvc 新手问题
- 访问文件和文件夹进入子目录后怎样保存上级目录?
- SMTP 服务器要求安全连接或客户端未通过身份验证。 服务器响应为: 5.5.1 Authentication Required. Learn more at
- 向高手请教!!!有关Email控件。。
- 如何安装SQL2005
- 求正则
ClubName通过什么和bbs表关联??
关系图!!我想以
BbsTitle,UsersName,ClubName,统计结果, 以统计来排序 注:结果为0 的显示为0例:
bbsTitle UsersName ClubName cnt
----------- --------- --------- -----------
frankyisman fengwe fengClub 2
Iamabody franky frankClub 1
我们在一起 franky fengClub 0
clubbbsid=clubbsid inner 后边自己写 表名最好起上别名
insert @users select 1,'franky'
union all select 2,'fengwe'declare @club table(clubid int ,clubname varchar(10))
insert @club select 1,'fengClub'
union all select 2,'frankClub'declare @clubBBS table(clubbbsid int,clubid int)
insert @clubbbs select 1,2
union all select 2,1declare @bbs table(bbsid int,clubbbsid int,bbstitle varchar(20),userid int)
insert @bbs select 1,2,'franky is man',2
union all select 2,1,'I am a boy',1
union all select 3,1,'我们在一起',1declare @bbsrep table(bbsrepid int,bbsid int,bbsreptitle varchar(50))
insert @bbsrep select 1,1,'Good'
union all select 2,1,'Very Good'
union all select 3,2,'No,Man'select d.bbstitle,a.username,b.clubname,'ent'=isnull(e.cnt,0)
from @bbs d left join @users a on d.userid=a.userid
left join @clubbbs c on d.clubbbsid=c.clubbbsid
left join @club b on b.clubid=c.clubid
left join (select bbsid,'cnt'=count(bbsid) from @bbsrep group by bbsid)e
on e.bbsid=d.bbsid
/*
bbstitle username clubname ent
-------------------- ---------- ---------- -----------
franky is man fengwe fengClub 2
I am a boy franky frankClub 1
我们在一起 franky frankClub 0(所影响的行数为 3 行)
*/
--1楼给的bbs表数据不全
declare @users table(userid int,username varchar(10))
insert @users select 1,'franky'
union all select 2,'fengwe'declare @club table(clubid int ,clubname varchar(10))
insert @club select 1,'fengClub'
union all select 2,'frankClub'declare @clubBBS table(clubbbsid int,clubid int)
insert @clubbbs select 1,2
union all select 2,1declare @bbs table(bbsid int,clubbbsid int,bbstitle varchar(20),userid int)
insert @bbs select 1,2,'franky is man',2
union all select 2,1,'I am a boy',1
union all select 3,2,'我们在一起',1declare @bbsrep table(bbsrepid int,bbsid int,bbsreptitle varchar(50))
insert @bbsrep select 1,1,'Good'
union all select 2,1,'Very Good'
union all select 3,2,'No,Man'select d.bbstitle,a.username,b.clubname,'ent'=isnull(e.cnt,0)
from @bbs d left join @users a on d.userid=a.userid
left join @clubbbs c on d.clubbbsid=c.clubbbsid
left join @club b on b.clubid=c.clubid
left join (select bbsid,'cnt'=count(bbsid) from @bbsrep group by bbsid)e
on e.bbsid=d.bbsid
/*
bbstitle username clubname ent
-------------------- ---------- ---------- -----------
franky is man fengwe fengClub 2
I am a boy franky frankClub 1
我们在一起 franky fengClub 0(所影响的行数为 3 行)*/
declare @users table(userid int,username varchar(10))
insert @users select 1,'franky'
union all select 2,'fengwe'declare @club table(clubid int ,clubname varchar(10))
insert @club select 1,'fengClub'
union all select 2,'frankClub'declare @clubBBS table(clubbbsid int,clubid int)
insert @clubbbs select 1,2
union all select 2,1declare @bbs table(bbsid int,clubbbsid int,bbstitle varchar(20),userid int)
insert @bbs select 1,2,'franky is man',2
union all select 2,1,'I am a boy',1
union all select 3,2,'我们在一起',1declare @bbsrep table(bbsrepid int,bbsid int,bbsreptitle varchar(50))
insert @bbsrep select 1,1,'Good'
union all select 2,1,'Very Good'
union all select 3,2,'No,Man'select d.bbstitle,a.username,b.clubname,'cnt'=isnull(e.cnt,0)
from @bbs d left join @users a on d.userid=a.userid
left join @clubbbs c on d.clubbbsid=c.clubbbsid
left join @club b on b.clubid=c.clubid
left join (select bbsid,'cnt'=count(bbsid) from @bbsrep group by bbsid)e
on e.bbsid=d.bbsid
order by 4 desc
/*
bbstitle username clubname cnt
-------------------- ---------- ---------- -----------
franky is man fengwe fengClub 2
I am a boy franky frankClub 1
我们在一起 franky fengClub 0(所影响的行数为 3 行)
*/