我有3个表
comments(id,question_id, user_Id) // here the user_id is of the user who has asked the question
questions(id,q_desc, user_id)
users(id, name, rank)
用户可以提出问题并对问题发表评论。
我需要一个报告，其中我想显示每个问题，最多有3个排名靠前的用户对此发表了评论，但提出问题的用户不应该出现在该特定问题的报告中，但他也有权对自己的问题发表评论
编辑：：：
Select * from comments inner join users(comments.user_id=u.id) group by question_id order by user.rank desc
它很乱，但它可以工作：
SELECT
a.question_id, a.user_id, a.name, a.rank
FROM
(
SELECT a.*, b.name, b.rank
FROM
(
SELECT DISTINCT b.question_id, b.user_id
FROM questions a
INNER JOIN comments b ON a.id = b.question_id AND a.user_id <> b.user_id
) a
INNER JOIN users b ON a.user_id = b.id
) a
INNER JOIN
(
SELECT a.question_id, b.rank
FROM
(
SELECT DISTINCT b.question_id, b.user_id
FROM questions a
INNER JOIN comments b ON a.id = b.question_id AND a.user_id <> b.user_id
) a
INNER JOIN users b ON a.user_id = b.id
) b ON a.question_id = b.question_id AND a.rank <= b.rank
GROUP BY
a.question_id, a.user_id, a.name, a.rank
HAVING
COUNT(1) <= 3
ORDER BY
a.question_id, a.rank DESC
编辑：这会产生相同的结果，而且更简洁：
SELECT a.*
FROM
(
SELECT DISTINCT a.question_id, a.user_id, b.name, b.rank
FROM comments a
INNER JOIN users b ON a.user_id = b.id
) a
INNER JOIN
questions b ON a.question_id = b.id AND a.user_id <> b.user_id
INNER JOIN
(
SELECT DISTINCT a.question_id, a.user_id, b.rank
FROM comments a
INNER JOIN users b ON a.user_id = b.id
) c ON b.id = c.question_id AND a.rank <= c.rank
GROUP BY
a.question_id, a.user_id, a.name, a.rank
HAVING
COUNT(1) <= 3
ORDER BY
a.question_id, a.rank DESC;
这些解决方案也适用于在同一问题上发布多条评论的用户。
请参阅SQLFiddle 上的两个解决方案