Question

一个Sql三表联合查询的问题

一个用户表 字段 users_id 、users_name
一个主贴表 字段 questions_id、question_content
一个回复表 字段 reply_id、questions_id、users_id、reply_content
我现在要用一条查询语句查出这样的结构
回复表的reply_id、回复表的questions_id、回复表的users_id、用户表的users_name(根据回复表users_id)、主贴表的question_content(根据回复表的question_id)
并根据回复表的users_id的数量排序 只要拍前面10位的

这样的语句该怎么写？

Answer 1

SELECT TOP 10 ht.replay_id,ht.questions_id,ht.users_id ,u.Users_name
FROM 回贴表 as ht INNER JOIN (
select questions_id,count(*) as 回复数 from 主贴表 as zb inner join 回复表 as hf
on zb.questions_id=hf.questions_id) as hc ON ht.questions_id=hc.questions_id
INNER JOIN 用户表 as u ON ht.user_id=u.User_id
ORDER BY hc.回复数 DESC

Answer 2

select top 10 from c.reply_id,c.questions_id,c.users_id,a.users_name,b.question_content
from 用户表 a,主贴表 b,回复表 c
where a.users_id = c.users_id
and b.questions_id = c.questions_id
order by users_id

Answer 3

select a.reply_id,a.questions_id,a.users_id,a. users_name from (select r.reply_id,r.questions_id,r.users_id,u.users_name from t_user u,t_reply r,t_question q where u.user=r.userid and r.questions_id=q.questions_id order by t.userid desc ) a where a.rownum<=10

Answer 4

这个，我不太清楚。。。