我一直在用这个查询试图让它输出嵌套的结果使用五个表，但已经失败了一次又一次我想我应该使用子查询，但我不理解他们足够把它们放在我的代码中使用。
我一直在绞尽脑汁，谁能告诉我怎样才能得到我想要的结果?
我需要输出我朋友的10个问题，其中5个是他们尊重的答案，以及1个类别和8个类别标签
下面是这10个输出的样子:
-
朋友:无名氏
- 类别:数学
- 标签: tag1 tag2, tag3等
- 问题1 + 1等于多少? 数学问题答案1数学问题答案2数学问题答案3数学问题答案4数学问题答案5
- 等等…
<?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，并将这些查询连接回表以获得其余的数据。然而，这样做我很挣扎，因为我不知道如果一个朋友只能给一个问题的答案。