在MySQL中的子查询上使用GROUP_CONCAT

问题描述：

我有一个MySQL查询，其中我想包含另一个表中ID的列表.人们可以在网站上添加某些项目，然后人们可以将这些项目添加到他们的收藏夹中.我基本上想获得最喜欢该商品的人的ID列表(这有点简化，但这可以归结为以下内容).

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).

基本上，我是这样的:

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

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

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:

1242-子查询返回多于1行

我认为这是使用GROUP_CONCAT而不是例如CONCAT的观点吗?我会用这种错误的方式吗?

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

好，谢谢您到目前为止的回答，这似乎行得通.但是，有一个陷阱.如果项目是由该用户添加的，则该项目也被视为最爱.因此，我需要进行其他检查，以检查creator = userid.有人可以帮我想出一种聪明(并且希望高效)的方法吗?

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?

谢谢！

我只是想这样做:

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

idlist 为空.请注意，如果我使用INNER JOIN而不是LEFT JOIN，则会得到空结果.即使我确定有满足ON要求的行.

And idlist is empty. Note that if I use INNER JOIN instead of LEFT JOIN I get an empty result. Even though I am sure there are rows that meet the ON requirement.

答

您无法在此类查询中访问外部作用域中的变量(不能在其中使用items.id).您应该尝试类似

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

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...).

在MySQL中的子查询上使用GROUP_CONCAT

相关推荐