在 MySQL 中对子查询使用 GROUP_CONCAT

Using GROUP_CONCAT on subquery in MySQL

提问by Aistina

I have a MySQL query in which I want to include a list of ID's from another table. On the website, people are able to add certain items, and people can then add those items to their favourites. I basically want to get the list of ID's of people who have favourited that item (this is a bit simplified, but this is what it boils down to).

我有一个 MySQL 查询，我想在其中包含另一个表中的 ID 列表。在网站上，人们可以添加某些项目，然后人们可以将这些项目添加到他们的收藏夹中。我基本上想获得喜欢该项目的人的 ID 列表（这有点简化，但归结起来就是这样）。

Basically, I do something like this:

基本上，我做这样的事情：

SELECT *,
GROUP_CONCAT((SELECT userid FROM favourites WHERE itemid = items.id) SEPARATOR ',') AS idlist
FROM items
WHERE id = $someid

This way, I would be able to show who favourited some item, by splitting the idlist later on to an array in PHP further on in my code, however I am getting the following MySQL error:

这样，我将能够通过稍后在我的代码中将 idlist 拆分为 PHP 中的数组来显示谁最喜欢某个项目，但是我收到以下 MySQL 错误：

1242 - Subquery returns more than 1 row

1242 - 子查询返回超过 1 行

I thought that was kind of the point of using GROUP_CONCATinstead of, for example, CONCAT? Am I going about this the wrong way?

我认为这是使用GROUP_CONCAT而不是例如CONCAT? 我会以错误的方式解决这个问题吗？

---

---

Ok, thanks for the answers so far, that seems to work. However, there is a catch. Items are also considered to be a favourite if it was added by that user. So I would need an additional check to check if creator = userid. Can someone help me come up with a smart (and hopefully efficient) way to do this?

好的，感谢到目前为止的答案，这似乎有效。但是，有一个问题。如果项目是由该用户添加的，则该项目也被视为收藏。所以我需要额外的检查来检查 creator = userid。有人能帮我想出一个聪明的（希望是有效的）方法来做到这一点吗？

Thank you!

谢谢！

Edit: I just tried to do this:

编辑：我只是尝试这样做：

SELECT [...] LEFT JOIN favourites ON (userid = itemid OR creator = userid)

And idlistis empty. Note that if I use INNER JOINinstead of LEFT JOINI get an empty result. Even though I am sure there are rows that meet the ON requirement.

而IDLIST是空的。请注意，如果我使用INNER JOIN而不是LEFT JOIN我得到一个空结果。即使我确定有满足 ON 要求的行。

采纳答案by soulmerge

You can't access variables in the outer scope in such queries (can't use items.idthere). You should rather try something like

您不能在此类查询中访问外部作用域中的变量（不能items.id在那里使用）。你应该尝试类似的东西

SELECT
    items.name,
    items.color,
    CONCAT(favourites.userid) as idlist
FROM
    items
INNER JOIN favourites ON items.id = favourites.itemid
WHERE
    items.id = $someid
GROUP BY
    items.name,
    items.color;

Expand the list of fields as needed (name, color...).

根据需要展开字段列表（名称、颜色...）。

回答by nietonfir

OP almost got it right. GROUP_CONCATshould be wrapping the columns in the subquery and not the complete subquery(I'm dismissing the separator because comma is the default):

OP 几乎是对的。GROUP_CONCAT应该包装子查询中的列而不是完整的子查询（我取消分隔符，因为逗号是默认值）：

SELECT i.*,
(SELECT GROUP_CONCAT(userid) FROM favourites f WHERE f.itemid = i.id) AS idlist
FROM items i
WHERE i.id = $someid

This will yield the desired result and also means that the accepted answer is partially wrong, because you can access outer scope variables in a subquery.

这将产生所需的结果，也意味着接受的答案部分错误，因为您可以在子查询中访问外部作用域变量。

回答by Turnkey

I think you may have the "userid = itemid" wrong, shouldn't it be like this:

我想你可能把“userid = itemid”弄错了，不应该是这样的：

SELECT ITEMS.id,GROUP_CONCAT(FAVOURITES.UserId) AS IdList
FROM FAVOURITES 
INNER JOIN ITEMS ON (ITEMS.Id = FAVOURITES.ItemId OR FAVOURITES.UserId = ITEMS.Creator)
WHERE ITEMS.Id = $someid
GROUP BY ITEMS.ID

回答by Ambrus Laszlo

Yes, soulmerge's solution is ok. But I needed a query where I had to collect data from more child tables, for example:

是的，soulmerge 的解决方案是可以的。但是我需要一个查询，我必须从更多子表中收集数据，例如：

• main table: sessions(presentation sessions) (uid, name, ..)
• 1st child table: eventswith key session_id (uid, session_uid, date, time_start, time_end)
• 2nd child table: accessories_needed(laptop, projector, microphones, etc.) with key session_id (uid, session_uid, accessory_name)
• 3rd child table: session_presenters(presenter persons) with key session_id (uid, session_uid, presenter_name, address...)

• 主表：sessions(presentation session) (uid, name, ..)
• 第一个子表：具有 key session_id 的事件（uid、session_uid、date、time_start、time_end）
• 第2子表：accessories_needed（笔记本电脑，投影仪，麦克风等）与主要的session_id（UID，session_uid，accessory_name）
• 第三个子表：session_presenters（演示者），带有 key session_id（uid、session_uid、presenter_name、address...）

Every Session has more rows in child tables tables (more time schedules, more accessories)

每个Session在子表表中有更多行（更多时间表，更多附件）

And I needed to collect in one collection for every session to display in ore row (some of them):

我需要为每个会话收集一个集合以显示在矿石行（其中一些）中：

session_id | session_name | date | time_start | time_end | accessories | presenters

My solution (after many hours of experiments):

我的解决方案（经过数小时的实验）：

SELECT sessions.uid, sessions.name,
    ,(SELECT GROUP_CONCAT( `events`.date SEPARATOR '</li><li>') 
            FROM `events` 
            WHERE `events`.session_id = sessions.uid ORDER BY `events`.date) AS date
    ,(SELECT GROUP_CONCAT( `events`.time_start SEPARATOR '</li><li>') 
            FROM `events` 
            WHERE `events`.session_id = sessions.uid ORDER BY `events`.date) AS time_start
    ,(SELECT GROUP_CONCAT( `events`.time_end SEPARATOR '</li><li>') 
            FROM `events` 
            WHERE `events`.session_id = sessions.uid ORDER BY `events`.date) AS time_end
    ,(SELECT GROUP_CONCAT( accessories.name SEPARATOR '</li><li>') 
            FROM accessories 
            WHERE accessories.session_id = sessions.uid ORDER BY accessories.name) AS accessories
    ,(SELECT GROUP_CONCAT( presenters.name SEPARATOR '</li><li>') 
            FROM presenters
            WHERE presenters.session_id = sessions.uid ORDER BY presenters.name) AS presenters

    FROM sessions

So no JOIN or GROUP BY needed. Another useful thing to display data friendly (when "echoing" them):

所以不需要 JOIN 或 GROUP BY。显示数据友好的另一个有用的事情（当“回应”它们时）：

• you can wrap the events.date, time_start, time_end, etc in "<UL><LI> ... </LI></UL>" so the "<LI></LI>" used as separator in the query will separate the results in list items.

• 您可以将 events.date、time_start、time_end 等包装在“<UL><LI> ... </LI></UL>”中，以便“<LI></LI>”用作查询中的分隔符将列表项中的结果分开。

I hope this helps someone. Cheers!

我希望这可以帮助别人。干杯!

回答by Turnkey

The purpose of GROUP_CONCAT is correct but the subquery is unnecessary and causing the problem. Try this instead:

GROUP_CONCAT 的目的是正确的，但子查询是不必要的并导致问题。试试这个：

SELECT ITEMS.id,GROUP_CONCAT(FAVOURITES.UserId)
FROM FAVOURITES INNER JOIN ITEMS ON ITEMS.Id = FAVOURITES.ItemId
WHERE ITEMS.Id = $someid
GROUP BY ITEMS.ID