此查询返回论坛问题/答案及其嵌套注释(类似于StackOverflow范例)。
SELECT forum_qa.*,
user_profiles.*,
c.*,
n.pid,
v.*,
Ifnull(n.ans_count, 0) AS ans_count
FROM forum_qa
JOIN user_profiles
ON user_id = forum_qa_author_id
LEFT JOIN (SELECT *
FROM votes) AS v
ON forum_qa_id = v.forum_qa_id_fk
LEFT JOIN (SELECT forum_cm_id,
forum_cm_author_id,
forum_qa_id_fk,
forum_cm_text,
forum_cm_timestamp,
forum_cm_flag,
first_name AS forum_cm_first_name,
last_name AS forum_cm_last_name,
facebook_id AS forum_cm_fb_id,
picture AS forum_cm_picture,
moderator AS forum_cm_moderator
FROM forum_cm
JOIN user_profiles
ON user_id = forum_cm_author_id) AS c
ON forum_qa_id = c.forum_qa_id_fk
LEFT JOIN (SELECT forum_qa_parent_id AS pid,
COUNT(*) AS ans_count
FROM forum_qa
WHERE forum_qa_parent_id IS NOT NULL
GROUP BY forum_qa_parent_id) AS n
ON forum_qa_id = n.pid
WHERE forum_qa_id LIKE "%"
AND forum_qa_parent_id IS NULL
ORDER BY forum_qa_timestamp DESC
LIMIT 0,3
我正在尝试对结果进行分页,并遇到以下问题:
通过在查询的末尾放置LIMIT,我最终限制了总行的数量,而不是问题/答案的数量。
例如,最后一行的LIMIT 0,3
给了我(qa:问题/答案;cm:评论):
forum_qa_id qa_text forum_cm_id cm_text
1 asd
2 wer 4 this is a comment
2 wer 5 this is another comment
不是forum_qa_id qa_text forum_cm_id cm_text
1 asd
2 wer 4 this is a comment
2 wer 5 this is another comment
3 zxc
3 zxc 7 yet another comment
任何建议如何修改我的查询,以便有LIMIT 0,3
返回不是3行但3个问题,无论有多少嵌套的评论?
随@michael的建议而改变
SELECT qa.*,
user_profiles.*,
c.*,
n.pid,
v.*,
Ifnull(n.ans_count, 0) AS ans_count
FROM (SELECT * FROM forum_qa LIMIT 0, 3) qa
JOIN user_profiles
ON user_id = qa.forum_qa_author_id
LEFT JOIN (SELECT *
FROM votes) AS v
ON qa.forum_qa_id = v.forum_qa_id_fk
LEFT JOIN (SELECT forum_cm_id,
forum_cm_author_id,
forum_qa_id_fk,
forum_cm_text,
forum_cm_timestamp,
forum_cm_flag,
first_name AS forum_cm_first_name,
last_name AS forum_cm_last_name,
facebook_id AS forum_cm_fb_id,
picture AS forum_cm_picture,
moderator AS forum_cm_moderator
FROM forum_cm
JOIN user_profiles
ON user_id = forum_cm_author_id) AS c
ON qa.forum_qa_id = c.forum_qa_id_fk
LEFT JOIN (SELECT forum_qa_parent_id AS pid,
COUNT(*) AS ans_count
FROM forum_qa
WHERE forum_qa_parent_id IS NOT NULL
GROUP BY forum_qa_parent_id) AS n
ON qa.forum_qa_id = n.pid
WHERE qa.forum_qa_id LIKE "%"
AND qa.forum_qa_parent_id IS NULL
ORDER BY qa.forum_qa_timestamp DESC
(希望)解决方案
读完这里
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/我决定将我想要的字段索引为ORDER BY
(forum_qa_type)—一旦我这样做了,查询将返回正确的问题数量。
谢谢@Michael的帮助。
假设forum_qa
是保存问题的表,您可以将其SELECT *
放入具有自己限制的子查询中。这当然是未经测试的,但原则上应该是可行的。
SELECT qa.*,
user_profiles.*,
c.*,
n.pid,
v.*,
Ifnull(n.ans_count, 0) AS ans_count
FROM (SELECT * FROM forum_qa LIMIT 0, 3) qa
JOIN user_profiles
ON user_profiles.user_id = qa.forum_qa_author_id
LEFT JOIN (SELECT *.....
-- etc...
对表名forum_qa
的其他引用需要更改为它的新别名qa