Question

问个sql基础问题? 有三个表User{Id,Name} Log{Id,UserId,content} Photo{Id,UserId,URL}

log表和photo表的UserId都是引用User表的Id

怎么查询出:
UserId ,UserName,LogCount,PhotoCount ?即
用户Id,用户名,日志总数,相片总数

Answer 1

select Id,Name,b.LogCount ,c.PhotoCount
from User a,
(select UserId,count(UserId) as LogCount from Log group by UserId) b,
(select UserId,count(UserId) as PhotoCount from Photo group by UserId) c
where a.Id=b.UserId(+) and a.Id=c.UserId(+)

追问

问个问题,把上面查出的结果按n条为一页分页,查出第m页怎么查?

追答

select Id,Name,b.LogCount ,c.PhotoCount from
(select rownum as num,Id,Name,b.LogCount ,c.PhotoCount
from User a,
(select UserId,count(UserId) as LogCount from Log group by UserId) b,
(select UserId,count(UserId) as PhotoCount from Photo group by UserId) c
where a.Id=b.UserId(+) and a.Id=c.UserId(+) ) t
where t.num between n*(m-1)+1 and n*m

Answer 2

select a.Id,a.Name,Count(b.id),Count(c.id)
from user a,log b,photo c
where a.id=b.userid and a.id=c.userid
group by a.id,a.name

追问

我测试了一下,你这个返回了一行数据,而且CountPhoto,countLog都不知道是什么怪数据

追答

select a.Id,a.Name,Count(b.id),Count(c.id)
from user a,log b,photo c，user d
where a.id=b.userid and d.id=c.userid
group by a.id,a.name

这个就对了，你测试

追问

user a 和user d干什么?

追答

其实我第一次写的代码就对了，这样的分组统计查询我写过几百条了。除非你数据有问题。
user表里的id 是log和 photo里的外键吧。

即
用户Id,用户名,日志总数,相片总数
count()函数用于统计指定记录的条数
count(photo.ID) 即是photo表中的记录总数、

追问

你这个返回的就一条:
19 sa 176 176
我的数据库里19号用户有8张照片22片日志 8*22=176
另外还有18号用户有一篇日志,没有图片
还有十几个用户,没有日志也没有图片

追答

select t1.id as 用户ID,t2.name as 用户名,日志总数,照片总数
from (select a.id,a.name,count(b.id)as 日志总数
from user a,log b
where b.userid=a.id
group by a.id,a.name)as t1
,(select a.id,a.name,count(c.id)as 照片总数
from user a,photo c
where c.userid=a.id
group by a.id,a.name) as t2
where t1.id=t2.id

Answer 3

select u.id,u.name,count(l.userid),count(p.userid) from user as u join log as l on u.id=l.userid join photo as p on u.id=p.userid group by l.userid,p.userid

追问

你的应该正确!
select a.Id,a.UserName,a.ImageFolderCount,count(b.Id) as LogCount from
(select UserT.Id,UserT.UserName,count(ImageFolder.Id) as ImageFolderCount from UserT
left join ImageFolder on ImageFolder.UserId=UserT.Id group by UserT.Id,UserT.UserName )as a
left join LogT as b on a.Id=b.UserId group by a.Id,a.UserName,a.ImageFolderCount
我也拼出来了