我一直在尝试使用此查询来使其使用五个表输出嵌套结果，但一次又一次失败，我想我应该使用子查询，但我对它们的理解不足以使其使用在我的代码中。
我一直在跳动我的头，请告诉我如何获得想要的结果。
我需要输出10个朋友问题以及5个受人尊敬的答案以及1个类别和8个类别标签
以下是10个输出的外观：
朋友：John Doe 1
分类：数学
标签：标签1标签2，标签3等等...
问题：1 +1是什么？
数学问题答案1
数学问题答案2
数学问题答案3
数学问题答案4
数学问题答案5
等等...
为了实现上述目的，我使用了以下MYSQL语句和PHP代码段
<?php
$query = $db->prepare(
"SELECT
question.post,
question.id,
question.user_id,
question_responses.id response_id,
question_responses.response,
category.id category_id,
category.category_name,
category.category_posts_id cpid,
tags.tag_name tn,
tags.id tagid,
friends.id myid,
friends.logged_username,
FROM (SELECT * FROM question ORDER BY question.id DESC LIMIT 10) AS question
LEFT JOIN friends ON friends.friend_id = question.user_id
LEFT JOIN (SELECT * FROM question_responses LIMIT 5) AS question_responses ON question_responses.question_response_id = question.id
LEFT JOIN category ON category.user_id = question.user_id
LEFT JOIN (SELECT * FROM tags LIMIT 8) AS tags ON tags.top_tags = category.category_id
WHERE friends.user_id = ?");
$id = "1";
$query->bindValue(1, $id, PDO::PARAM_INT);
try {
$query->execute();
$questions = array();
$question_responses = array();
$tag = array();
while($row = $query->fetch(PDO::FETCH_ASSOC)) {
$question_id = $row['id'];
$tags_id = $row['tagid'];
$questions[$question_id] = $row;
$tags[$tags_id][] = $row;
$question_responses[$question_id][] = $row;
}
foreach ($questions as $question_id => $row) {
foreach ($tag as $tags_id => $row) {
echo $row['tn'];
}
echo "<b>".$row['post']."</b></br></br>";
foreach ($question_responses[$question_id] as $response_id => $row) {
echo $row['response']."</br></br>";
}
}
} catch (PDOException $e) {
echo $e->getMessage();
exit();
}
?>
在此先感谢您的帮助。
最佳答案
使用大量用户变量的可行方法。这可能会很慢。它具有子查询，这些子查询将返回的结果加一个计数，然后丢弃ON子句中不需要的结果。
SELECT
question.post,
question.id,
question.user_id,
question_responses.id response_id,
question_responses.response,
category.category_id,
category.category_name,
category.cpid,
category.tn,
tagstagid,
friends.id myid,
friends.logged_username
FROM friends
INNER JOIN
(
SELECT category.user_id,
category.id AS category_id,
category.category_name,
category.category_posts_id AS cpid,
tags.tag_name AS tn,
tags.id AS tagid,
@category_user_id_cnt:=IF(@category_user_id = user_id, @category_user_id_cnt + 1, 1) AS category_user_id_cnt,
@tag_cnt:=IF(@category_user_id = user_id, @tag_cnt + 1, 1) AS tag_cnt,
@category_user_id := user_id
FROM category
CROSS JOIN (SELECT @category_user_id=0, @category_user_id_cnt:=0, @tag_cnt:=0) sub0
LEFT OUTER JOIN tags
ON tags.top_tags = category.category_id
) AS category
ON category.user_id = friends.user_id
AND category.category_user_id_cnt = 1
AND category.tag_cnt <= 8
LEFT JOIN
(
SELECT post,
id,
user_id,
@question_user_id_cnt:=IF(@question_user_id = user_id, @question_user_id_cnt + 1, 1) AS question_user_id_cnt,
@question_user_id := user_id
FROM
(
SELECT post,
id,
user_id
FROM question
ORDER BY user_id, id DESC
) sub_question
CROSS JOIN (SELECT @question_user_id=0, @question_user_id_cnt:=0) sub0
) AS question
ON friends.friend_id = question.user_id
AND question.question_user_id_cnt <= 10
LEFT JOIN
(
SELECT id response_id,
question_responses.response,
@question_response_cnt:=IF(@question_response_id = question_response_id, @question_response_cnt + 1, 1) AS question_response_cnt,
@question_response_id := question_response_id
FROM question_responses
CROSS JOIN (SELECT @question_response_id=0, @question_response_cnt:=0) sub0
) AS question_responses ON question_responses.question_response_id = question.id AND question_responses.question_response_cnt <= 5
WHERE friends.user_id = ?
可能有可能使用GROUP_CONCAT和SUBSTRING_INDEX的技巧来做一个更快的版本，以使子查询获得最新的X id，然后将它们重新加入表中以获取其余数据。然而，这样做我很挣扎，因为我不知道朋友是否只能对一个问题给出一个答案。
关于php - MYSQL使用四个LEFT JOINS和LIMIT CLAUSES显示嵌套结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24857848/