mysql - 通过 1 个查询获取 2 个相关组？

Question

我有以下场景:

• 1个项目表
• 1个成员表
• 1 页表

每个“item”都属于一个“member”和一个“page”

$query = mysql_query("SELECT i.id, i.title, mem.realname, p.pagename
                FROM items AS i
                LEFT JOIN members AS mem ON (mem.ID_MEMBER = i.author)
                LEFT JOIN pages AS p ON (p.id = i.page)
                WHERE i.id LIKE '$item_id'");

这提供了我需要的基本信息:

• id: 3, title: 音乐视频, realname: Peter Smith, pagename: Youtube

所以我要做的是获得 2 个独立的“3 个相关项目”组，其中:

• id.author = Peter Smith
• id.page = Youtube

所以输出应该是这样的:

• id:3，标题:音乐视频，实名:Peter Smith，页面名称:Youtube

• Peter Smith 的项目:项目 5、项目 4、项目 8(随机显示)

• 来自 Youtube 的项目:项目 1、项目 2、项目 9(随机显示)

我是否需要执行 2 个额外的查询才能获取此信息？或者我可以将它打包到所有一个查询中吗？

非常感谢!

更新:

我添加了@valex 建议的 Group_concat

   SELECT i.id, i.title, mem.realname, p.pagename,
          ( select CONCAT('Items from ', mem.realname,': ',
                (select GROUP_CONCAT(Title) 
                        FROM
                           (Select Title from items where author=
                                    (select author from items 
                                              WHERE id = '$item_id' LIMIT 1)
                             ORDER BY RAND() LIMIT 3) T)              
                )
           ) as items_from_author,
          ( select CONCAT('Items from ',p.pagename,': ',
                (select GROUP_CONCAT(Title) 
                        FROM
                           (Select Title from items where page=
                                        (select page from items 
                                              WHERE id = '$item_id' LIMIT 1)

                             ORDER BY RAND() LIMIT 3) T)              
                )
           ) as items_from_page


            FROM items AS i
            LEFT JOIN members AS mem ON (mem.ID_MEMBER = i.author)
            LEFT JOIN pages AS p ON (p.id = i.page)
            WHERE i.id LIKE '$item_id'

但是得到如下错误:

Invalid query: Unknown column 'i.author' in 'where clause'

有什么想法吗？

更新 2

目前正在使用@valex 建议的“4 级”查询。有没有办法让它在 2 个级别上工作？

SELECT i.id, i.title, mem.realname, p.pagename,
          ( select CONCAT('Items from ', mem.realname,': ',
                (select GROUP_CONCAT(Title) 
                        FROM
                           (Select Title from items where author=
                                    (select author from items 
                                              WHERE id = '$item_id' LIMIT 1)
                             ORDER BY RAND() LIMIT 3) T)              
                )
           ) as items_from_author,
          ( select CONCAT('Items from ',p.pagename,': ',
                (select GROUP_CONCAT(Title) 
                        FROM
                           (Select Title from items where page=
                                        (select page from items 
                                              WHERE id = '$item_id' LIMIT 1)

                             ORDER BY RAND() LIMIT 3) T)              
                )
           ) as items_from_page


            FROM items AS i
            LEFT JOIN members AS mem ON (mem.ID_MEMBER = i.author)
            LEFT JOIN pages AS p ON (p.id = i.page)
            WHERE i.id LIKE '$item_id'

似乎可以通过某种方式对其进行优化。有什么想法吗？

Answer 1

您可以使用 GROUP_CONCAT()函数将这些列表作为当前行中的字段获取。

例如:

SELECT i.id, i.title, mem.realname, p.pagename,
              ( select CONCAT('Items from ', mem.realname,': ',
                    (select GROUP_CONCAT(Title) 
                            FROM
                               (Select Title from items where author=i.author
                                 ORDER BY RAND() LIMIT 3) T)              
                    )
               ) as items_from_author,
              ( select CONCAT('Items from ',p.pagename,': ',
                    (select GROUP_CONCAT(Title) 
                            FROM
                               (Select Title from items where page=i.page
                                 ORDER BY RAND() LIMIT 3) T)              
                    )
               ) as items_from_page


                FROM items AS i
                LEFT JOIN members AS mem ON (mem.ID_MEMBER = i.author)
                LEFT JOIN pages AS p ON (p.id = i.page)
                WHERE i.id LIKE '$item_id'

Update: MySQL 好像不能通过两层访问外层的查询字段。所以尝试使用以下内容:

SELECT i.id, i.title, mem.realname, p.pagename,
              ( select CONCAT('Items from ', mem.realname,': ',
                    (select GROUP_CONCAT(Title) 
                            FROM
                               (Select Title from items where author=
                                        (select author from items 
                                                  WHERE id = '$item_id' LIMIT 1)
                                 ORDER BY RAND() LIMIT 3) T)              
                    )
               ) as items_from_author,
              ( select CONCAT('Items from ',p.pagename,': ',
                    (select GROUP_CONCAT(Title) 
                            FROM
                               (Select Title from items where page=
                                            (select page from items 
                                                  WHERE id = '$item_id' LIMIT 1)

                                 ORDER BY RAND() LIMIT 3) T)              
                    )
               ) as items_from_page


                FROM items AS i
                LEFT JOIN members AS mem ON (mem.ID_MEMBER = i.author)
                LEFT JOIN pages AS p ON (p.id = i.page)
                WHERE i.id LIKE '$item_id'