表不太清楚,是这样的!!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!
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!
解决方案 »
- MFC中执行SQL 2005查询sql时报错:对象名无效,未能准备语句
- 紧急求救.........sqlserver2000.索引出错.
- 2个机器上的MSSQL2005数据库,数据库名一致,怎么复制表到另外一个数据库
- 关于分类的查询性能怎样优化
- 谁可以帮我我吗?
- sql 这样的排序要求应该如何实现
- 一般在MSSQL里,大家在数据设计时候对于货币值用啥样的类型,精度怎么处理?
- 如何把记录编号作为一个新字段插入到表中?
- 请问出现以下请示,如何设置ODBC 还是修改其它地方。ACCESS数据库
- 变量赋值问题
- 跪求方法,删除了一个库的sysuser表里的ado这个记录,怎么恢复
- 一个关于把数据导出到word或excel的问题
--> 测试数据: #Users
if object_id('tempdb.dbo.#Users') is not null drop table #Users
create table #Users (UsersID int,UsersName varchar(6))
insert into #Users
select 1,'franky' union all
select 2,'fengwe'
--> 测试数据: #Club
if object_id('tempdb.dbo.#Club') is not null drop table #Club
create table #Club (ClubID int,ClubName varchar(9))
insert into #Club
select 1,'fengClub' union all
select 2,'frankClub'
--> 测试数据: #ClubBBS
if object_id('tempdb.dbo.#ClubBBS') is not null drop table #ClubBBS
create table #ClubBBS (ClubBBSID int,ClubID int)
insert into #ClubBBS
select 1,2 union all
select 2,1
--> 测试数据: #Bbs
if object_id('tempdb.dbo.#Bbs') is not null drop table #Bbs
create table #Bbs (BbsID int,BbsTitle varchar(11),UsersID int)
insert into #Bbs
select 1,'frankyisman',2 union all
select 2,'Iamabody',1
--> 测试数据: #BbsRep
if object_id('tempdb.dbo.#BbsRep') is not null drop table #BbsRep
create table #BbsRep (BbsRepID int,BbsID int,BbsRepTitle varchar(10))
insert into #BbsRep
select 1,1,'Good!' union all
select 2,1,'VeryGood!' union all
select 3,2,'Noman!'select u.usersname,c.clubname,count(*) cnt
from #Club c
join #ClubBBS cb
on c.ClubID=cb.ClubID
join #Bbs bbs
on cb.ClubBBSID=bbs.BbsID
join #BbsRep br
on bbs.BbsID=br.BbsID
join #users u
on bbs.usersID=u.UsersID
group by u.usersname,c.clubname
order by count(*) desc
/*
usersname clubname cnt
--------- --------- -----------
fengwe frankClub 2
franky fengClub 1(2 行受影响)
*/
BbsTitle,UsersName,ClubName,统计结果, 以统计来排序
if object_id('tempdb.dbo.#Users') is not null drop table #Users
create table #Users (UsersID int,UsersName varchar(6))
insert into #Users
select 1,'franky' union all
select 2,'fengwe'
--> 测试数据: #Club
if object_id('tempdb.dbo.#Club') is not null drop table #Club
create table #Club (ClubID int,ClubName varchar(9))
insert into #Club
select 1,'fengClub' union all
select 2,'frankClub'
--> 测试数据: #ClubBBS
if object_id('tempdb.dbo.#ClubBBS') is not null drop table #ClubBBS
create table #ClubBBS (ClubBBSID int,ClubID int)
insert into #ClubBBS
select 1,2 union all
select 2,1
--> 测试数据: #Bbs
if object_id('tempdb.dbo.#Bbs') is not null drop table #Bbs
create table #Bbs (BbsID int,ClubBBSID int,BbsTitle varchar(11),UsersID int)
insert into #Bbs
select 1,2,'frankyisman',2 union all
select 2,1,'Iamabody',1
--> 测试数据: #BbsRep
if object_id('tempdb.dbo.#BbsRep') is not null drop table #BbsRep
create table #BbsRep (BbsRepID int,BbsID int,BbsRepTitle varchar(10))
insert into #BbsRep
select 1,1,'Good!' union all
select 2,1,'VeryGood!' union all
select 3,2,'Noman!'select bbs.bbsTitle,u.UsersName,c.ClubName,count(*) cnt
from #Users u
join #Bbs bbs
on u.UsersID=bbs.UsersID
join #ClubBBS cb
on bbs.ClubBBSID=cb.ClubBBSID
join #BBSRep br
on bbs.bbsID=br.BBSID
join #Club c
on cb.ClubID=c.ClubID
group by bbs.bbsTitle,u.UsersName,c.ClubName
order by count(*) desc
/*
bbsTitle UsersName ClubName cnt
----------- --------- --------- -----------
frankyisman fengwe fengClub 2
Iamabody franky frankClub 1(2 行受影响)*/
----------- --------- --------- -----------
frankyisman fengwe fengClub 2
Iamabody franky frankClub 1
我们在一起 franky fengClub 0这样显示!!!??????
if object_id('tempdb.dbo.#Users') is not null drop table #Users
create table #Users (UsersID int,UsersName varchar(6))
insert into #Users
select 1,'franky' union all
select 2,'fengwe'
--> 测试数据: #Club
if object_id('tempdb.dbo.#Club') is not null drop table #Club
create table #Club (ClubID int,ClubName varchar(9))
insert into #Club
select 1,'fengClub' union all
select 2,'frankClub'
--> 测试数据: #ClubBBS
if object_id('tempdb.dbo.#ClubBBS') is not null drop table #ClubBBS
create table #ClubBBS (ClubBBSID int,ClubID int)
insert into #ClubBBS
select 1,2 union all
select 2,1
--> 测试数据: #Bbs
if object_id('tempdb.dbo.#Bbs') is not null drop table #Bbs
create table #Bbs (BbsID int,ClubBBSID int,BbsTitle nvarchar(11),UsersID int)
insert into #Bbs
select 1,2,'frankyisman',2 union all
select 2,1,'Iamabody',1 union all
select 3,1,N'这个没人回复',1
--> 测试数据: #BbsRep
if object_id('tempdb.dbo.#BbsRep') is not null drop table #BbsRep
create table #BbsRep (BbsRepID int,BbsID int,BbsRepTitle varchar(10))
insert into #BbsRep
select 1,1,'Good!' union all
select 2,1,'VeryGood!' union all
select 3,2,'Noman!'select bbs.bbsTitle,u.UsersName,c.ClubName,isnull(count(br.bbsid),0) cnt
from #Bbs bbs
left join #Users u
on u.UsersID=bbs.UsersID
left join #ClubBBS cb
on bbs.ClubBBSID=cb.ClubBBSID
left join #BBSRep br
on bbs.bbsID=br.BBSID
left join #Club c
on cb.ClubID=c.ClubID
group by bbs.bbsTitle,u.UsersName,c.ClubName
order by count(*) desc
/*
bbsTitle UsersName ClubName cnt
----------- --------- --------- -----------
frankyisman fengwe fengClub 2
Iamabody franky frankClub 1
这个没人回复 franky frankClub 0
*/
select bbs.BbsID,bbs.bbsTitle,u.UsersName,c.ClubName,count(*) cnt
from Users u
left join Bbs bbs
on u.UsersID=bbs.UsersID
left join ClubBBS cb
on bbs.ClubBBSID=cb.ClubBBSID
left join BBSRep br
on bbs.bbsID=br.BBSID
left join Club c
on cb.ClubID=c.ClubID
group by bbs.BbsID,bbs.bbsTitle,u.UsersName,c.ClubName
order by count(*) desc
结果却是这样:
bbsTitle UsersName ClubName cnt
----------- --------- --------- -----------
frankyisman fengwe fengClub 2
Iamabody franky frankClub 1
我们在一起 franky fengClub 1这是为什么呢?
from Users u
left join Bbs bbs
on u.UsersID=bbs.UsersID
left join ClubBBS cb
on bbs.ClubBBSID=cb.ClubBBSID
left join BBSRep br
on bbs.bbsID=br.BBSID
left join Club c
on cb.ClubID=c.ClubID
group by bbs.BbsID,bbs.bbsTitle,u.UsersName,c.ClubName
order by count(br.BbsID) desc好了,弄好了!!