join + count + group + order
解决方案 »
- select @StockId=FHeadSelfS0144,@UniteStockName=b.F_102 from这是什么意思呢?
- [2008/07/18]sql code
- 用ACCESS插入语句,有点挑战性,在线等
- 有史以来,我碰到的最让我头痛的问题
- 如何实现sql数据表的数据分批次调入内存?
- 请教一个比较难的查询问题
- 存储过程 根据a,b表添加更新c表.~~~~~~~~~~~~~~~~~~
- 求教sql 语句
- *****请教一条查询语句******
- 求助:MS SQL Server no data was found for satisfy the request这是什么原因引起的错误
- 高分求一段触发器代码
- 急救!关于delphi的查询问题
from Users u
left join Bbs bbs on u.UsersID=bbs.UsersID
left join ColumnNews cn on u.UsersID=cn.UsersID
left join Comments cs on u.UsersID=cs.UsersID
left join (select BbsID,'cnt'=count(BbsID) from BbsRep group by BbsID)e on e.BbsID=bbs.BbsID
left join (select UsersID,'bnt'=count(BbsID) from Bbs group by UsersID)b on b.UsersID=bbs.UsersID
left join (select UsersID,'cnnt'=count(UsersID) from ColumnNews group by UsersID)cc on cc.UsersID=u.UsersID
left join (select UsersID,'csnt'=count(UsersID) from Comments group by UsersID)css on css.UsersID=u.UsersID我写的语句,显示不正确!!!帮我看一下!!谢谢!
,count(distinct b.BbsID) as CountBbs
,count(c.BbsRepID) as CountBbsRep
,count(d.ColumnNewsID) as CountColumnNews
,count(e.CommentsID) as CountComments
,count(f.ImagesInfoID) as CountImagesInfo
,count(distinct b.BbsID)+count(c.BbsRepID)+count(d.ColumnNewsID)+count(e.CommentsID)+count(f.ImagesInfoID) as Total
from Users a
left join Bbs b on b.UsersID =a.UsersID
left join BbsRep c on c.BbsID =b.BbsID
left join ColumnNews d on d.UsersID =a.UsersID
left join Comments e on e.UsersID =a.UsersID
left join ImagesInfo f on f.UsersID =a.UsersID
group by a.UsersID ,a.UsersName
谢谢!!!学习!!!
请问有没有什么好点儿书或是电子文档,学习SQL ???
我知道我的数据库操作很菜!!想学习一下!请指教!
declare @Users table([UsersID] int,[UsersName] nvarchar(6))
Insert @Users
select 1,N'franky' union all
select 2,N'xiang' union all
select 3,N'feng'
--Select * from @Usersdeclare @Bbs table([BbsID] int,[UsersID] int)
Insert @Bbs
select 1,2 union all
select 2,1
--Select * from @Bbsdeclare @BbsRep table([BbsRepID] int,[BbsID] int)
Insert @BbsRep
select 1,1 union all
select 2,1 union all
select 3,2
--Select * from @BbsRepdeclare @ColumnNews table([ColumnNewsID] int,[UsersID] int)
Insert @ColumnNews
select 1,2 union all
select 2,2 union all
select 3,1
--Select * from @ColumnNewsdeclare @Comments table([CommentsID] int,[UsersID] int)
Insert @Comments
select 1,1 union all
select 2,1
--Select * from @Comments
declare @ImagesInfo table([ImagesInfoID] int,[UsersID] int)
Insert @ImagesInfo
select 1,2 union all
select 2,2 union all
select 3,1
--Select * from @ImagesInfo--UsersID ¦ UsersName ¦ CountBbs ¦ CountBbsRep ¦ CountColumnNews ¦ CountComments ¦ CountImagesInfo ¦ Total select u.UsersID,u.UsersName,
'ent' = (select count(1) from @Bbs where UsersID = u.UsersID),
'bb' = (select count(1) from @BbsRep r left join @Bbs b on b.BbsID =r.BbsID where UsersID = u.UsersID),
'cc' = (select count(1) from @ColumnNews where UsersID = u.UsersID),
'css' = (select count(1) from @Comments where UsersID = u.UsersID),
'image' = (select count(1) from @ImagesInfo where UsersID = u.UsersID)from @Users u
/*
UsersID UsersName ent bb cc css image
----------- --------- ----------- ----------- ----------- ----------- -----------
1 franky 1 1 1 2 1
2 xiang 1 2 2 0 2
3 feng 0 0 0 0 0
*/
---------------------------------------------------------------------------------------------
select *,total=CountBbs+CountBbsRep+CountColumnNews+CountComments+CountImagesInfo
from (
select
usersid = u.usersid,
usersname = u.usersname,
countbbs = isnull((select count(*) from bbs where usersid = u.usersid),0),
countbbsrep = isnull((select count(*) from bbs a,bbsrep b
where a.bbsid = b.bbsid and a.usersid = u.usersid),0),
countcolumnnews = isnull((select count(*) from ColumnNews
where usersid = u.usersid),0)
countComments = isnull((select count(*) from comments
where usersid = u.usersid),0)
countImagesInfo = isnull((select count(*) from ImagesInfo
where usersid = u.usersid),0)
) a