一条选择语句中的多级MYSQL查询 [英] Multi-Level MYSQL Query In One Select Statement

问题描述

这里的SQL问题非常简单-我在三个表中分布了三个级别的数据. 表1-列表， 表2-兴趣， 表3-消息

Pretty straightforward SQL question here - I have three levels of data spread out over three tables. Table 1- Listings, Table 2 - Interests, Table 3 - Messages

表1->表2和表2->表3具有一对多关系

Table 1 -> Table 2, and Table 2 -> Table 3 have a One-To-Many Relationship

总会有列表，但是每个列表可能有或没有兴趣，并且每种兴趣可能有或没有消息.

我想做的是选择 1 Select Statement 中的所有信息，以避免多次数据库调用.

What I want to do is select all the information in 1 Select Statement in order to avoid multiple database calls.

我知道，通过两个级别的信息，您可以使用GROUP_CONCAT和GROUP BY将所有第二个级别的信息抓取到连接的字符串列中.如果我只想使用一个选择，我只是不知道该如何处理三个级别. 我也知道如何通过两个单独的调用以及介于两者之间的PHP/python脚本来完成此操作，但是我想避免这样做以提高性能.

I know with two levels of information you can use GROUP_CONCAT and GROUP BY to grab all the second level information into a concatenated string column. I just don't know what to do with three levels if I want to use only one select. I also know how this can be done with two separate calls and a PHP/python script in between, but I want to avoid that for performance.

理想情况下，我会返回一个看起来像这样的数组:

Ideally I would get back an array that looks like this:

array(
     '1'=>array(listingId'=>1, 
                interests=>
                           array(
                                 array(
                                       'interestId'=>2
                                       'messages'=>array(
                                                         'messageId'=>5,
                                                         'message'=>'hi'
                                                         ),
                                                   ...
                                       ),
                                ...
                                ),
                          ...
                ),
     ...
     )

另一个理想的情况是:

 array(
     '1'=>array(listingId'=>1, 
                interests=>
                           array(
                                 array('interestId'=>2,
                                       'messages'=>INSERT CONCATENATED STRING HERE
                                      ),
                                 ...
                                ),
                           ...
                ),
     ...
     )

在这种情况下，我只能使用PHP/Python/Ruby脚本来解析串联的消息字符串.

With this situation I could just use a PHP/Python/Ruby script to parse the concatenated string of message.

点代表具有相似数据的更多阵列.

尝试使用串联连接时最重要的问题是只能使用子选择拉回一列.

The most significant problem with trying to use concatenation is that you can only pull back one column using a subselect.

提前谢谢！ :)

推荐答案

难道不能只加入所有表吗?

Can't you just join all the tables?

SELECT * FROM table1, table2, table3 WHERE table3.ref=table2.id AND table2.ref=table1.id

我不知道您使用哪个外部索引，所以我只使用了ref和id.

I don't know which foreign index you use, so I just used ref and id.

一次调用中的另一种解决方案是使用不同的GROUP_CONCAT指令，并使用不同的分隔符，如下所示:

Another solution, in 1 call, is using different GROUP_CONCAT instructions, with different separators, like this:

SELECT naam,GROUP_CONCAT(t4.messages SEPARATOR '#') FROM test_listing t1 LEFT JOIN (SELECT t2.id,t2.ref,GROUP_CONCAT(message SEPARATOR '=') as messages FROM test_message t3 RIGHT JOIN test_interest t2 ON t2.id=t3.ref GROUP BY t2.id) t4 ON t1.id=t4.ref GROUP BY t1.id

这将返回与列表一样多的记录，然后将所有消息与分隔符'='合并，之后将所有这些消息与'#'合并

This will return as many records as you have listings, and then combines all messages with separator '=', after which it will combine all these with '#'

我看不到这个问题的更优雅的解决方案！而且，对于将表加入表嵌套数组[PHP/MYSQL] .

I don't see a more elegant solution to this problem ! Also, neither is there a more elegant solution on the topic Join Table To a nested array [PHP/MYSQL].

这篇关于一条选择语句中的多级MYSQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！