对查询结果进行子查询?

本文介绍了对查询结果进行子查询?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我如何将这两者结合起来?

How do I unite these two?

这个查询:

SELECT f1.asked_user_id AS friend_id FROM friends AS f1 JOIN friends AS f2 ON f1.asked_user_id = f2.asker_user_id AND f1.asker_user_id = f2.asked_user_id AND f1.asker_user_id = :user_id WHERE f1.status = 1 AND f2.status = 1

与:

(SELECT GROUP_CONCAT(words_en.word) FROM connections JOIN words_en ON connections.word_id = words_en.id WHERE connections.user_id = friends.friend_id) As friend_words

再做一次加入:

JOIN users ON friends.friend_id = users.id

我在这里尝试了几件事:www.sqlfiddle/#!2/85d6d/36

I've tried few things here: www.sqlfiddle/#!2/85d6d/36

第一个查询从表中选择两种方式的朋友.我从那个查询中得到了friend_id.现在我想更多地了解这个friend_id.所以我想用这个 id 做另一个查询，该查询将显示该用户连接表中的所有 word_id，word_ids 可用于从 words_en 中获取实际单词.最后，我想从 users 表中获取用户 name_surname.

First query selects two way friends from the table. I get friend_id from that query. Now I want to know more about this friend_id. So I want to use this id to do another query that would display all word_ids from connections table by that user, word_ids can be used to get actual words from words_en. And lastly, I want to get users name_surname from users table.

推荐答案

试试这个:

SELECT a.name_surname,GROUP_CONCAT(Distinct w.word Order by w.word asc) AS words FROM ( SELECT f1.asked_user_id AS friend_id, f1.created, u.name_surname, u.avatar FROM friends AS f1 INNER JOIN friends AS f2 ON f1.asked_user_id = f2.asker_user_id INNER JOIN users AS u ON f1.asked_user_id = u.id AND f1.asker_user_id = f2.asked_user_id AND f1.asker_user_id = 1 WHERE f1.status = 1 AND f2.status = 1 ) a LEFT JOIN connections c ON c.user_id = a.friend_id LEFT JOIN words_en w ON c.word_id = w.id GROUP BY 1;

sqlfiddle 演示

有了您的原始查询，我所做的是将该查询视为表 (a) 并将 LEFT JOIN 它与 connections 表.然后，LEFT JOIN connections 表与 words_en 表以达到所需的单词.这样就可以获取所有从您的原始查询返回的用户，即使没有联系/字词也是如此.

Having your original query, what i did is treat that query as a table (a) and LEFT JOIN it with connections table. Then, LEFT JOIN the connections table with the words_en table to reach the desired words. This makes it possible to get all users that return from your original query, even when there are no connections/words.