求一个sql用户表:users
uid,username主题表subjects
sid,title,uid回复表 replies
rid,title,uid请问怎么才能取得如下的用户排行,即同时获取用户的主题数和回复数,前提是用户表中没有这些缓存
user subjects replies
user1 10 20
user2 5 10
uid,username主题表subjects
sid,title,uid回复表 replies
rid,title,uid请问怎么才能取得如下的用户排行,即同时获取用户的主题数和回复数,前提是用户表中没有这些缓存
user subjects replies
user1 10 20
user2 5 10
select users.username as user ,subjects.sid as sid replies.rid from users,subjects,replies where users.uid=sujects.uid and subjects.title=replies.title and users.uid=replies.uid ) group by user,sid
select a.uid,subjects,replies from
(select uid,count(sid) as subjects from subjects group by uid)as a
inner join
(select uid,count(rid) as replies from replies grou by uid)
on a.uid=b.uid
select users.uid as users,subjects,replies from
((select uid,count(sid) as subjects from subjects group by uid)as a
inner join
(select uid,count(rid) as replies from replies grou by uid)as b
on a.uid=b.uid)inner join users on a.uid=user.uid
上面那个漏了点东西
主题表:subjects(sid,title,uid)
回复表:replies(rid,title,uid)
---- 有个疑问: replies中的uid是指回复者的id? 哪个回复隶属于哪个主题是否是通过title和主表相关联? (好像title不好唯一定位吧?)请问怎么才能取得如下的用户排行,即同时获取用户的主题数和回复数,前提是用户表中没有这些缓存
---- 有个疑问: 用户的..回复数是指用户的回复还是指用户主题的回复?[假设 1]: 要的是“用户主题的回复”,且replies中的uid是指回复者的id,回复和主题通过title关联:
select
username as user
,count(distinct sid) as subjets
,count(1) as replies
from users u
join subjects s on uid=u.uid
join replies r on title=s.title
[/假设 1]
[假设 2]: 要的是“用户的回复”,且replies中的uid是指回复者的id:
参考楼上的
[/假设 2]