MySQL，聚合子选择问题

发布于 2024-10-11 15:55:08 字数 625 浏览 12 评论 0原文

我正在开发一个网站，允许特定游戏的玩家上传他们的关卡并为其添加标签。每个玩家的帐户实际上是该网站正在使用的论坛（SMF）中的一个帐户。

我能够毫无问题地返回与特定级别关联的所有标签；当我想过滤那些与子选择结果匹配的内容时，我遇到了问题。它声称“taglist”列不存在...

SELECT smf_members.realName,game_levels.*,
       (SELECT GROUP_CONCAT(tag) 
          FROM `game_tags`
         WHERE `game_tags`.uuid = `game_levels`.uuid) AS taglist    
  FROM `game_levels`
INNER JOIN `smf_members` ON `smf_members`.ID_MEMBER = `game_levels`.ID_MEMBER    
WHERE taglist LIKE 'untagged'    
ORDER BY `ID_TOPIC` DESC

提前致谢。我还尝试在标签表上执行第二个 INNER JOIN，通过在 game_tags.tag 上使用常规 WHERE 来缩小结果范围，但最终我得到了将所有标签连接在一起的单行。

原文

I'm in the process of developing a site that will allow players of a certain game to upload their levels and tag them. Each player's account is actually an account in the forums that the site is using (SMF).

I am able to return all tags associated with a particular level no problems; I run into an issue when I want to filter those matching on the result of that subselect. It claims the column 'taglist' doesn't exist...

SELECT smf_members.realName,game_levels.*,
       (SELECT GROUP_CONCAT(tag) 
          FROM `game_tags`
         WHERE `game_tags`.uuid = `game_levels`.uuid) AS taglist    
  FROM `game_levels`
INNER JOIN `smf_members` ON `smf_members`.ID_MEMBER = `game_levels`.ID_MEMBER    
WHERE taglist LIKE 'untagged'    
ORDER BY `ID_TOPIC` DESC

Thanks in advance. I have also tried doing a second INNER JOIN on the tags table, narrowing the results by using a regular WHERE on game_tags.tag, but then I end up with a single row that has all the tags concatenated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

安穩 2024-10-18 15:55:08

您不能在 WHERE 子句中引用列别名 - MySQL 最早支持列别名引用的是 GROUP BY。使用：

  SELECT sm.realName, 
         gl.*,
         x.taglist
    FROM GAME_LEVELS gl
    JOIN SMF_MEMBERS sm ON sm.id_member = gl.id_member
    JOIN (SELECT gt.uuid,
                 GROUP_CONCAT(gt.tag) AS taglist
            FROM GAME_TAGS gt
        GROUP BY gt.uuid) x ON x.uuid = gl.uuid
   WHERE x.taglist LIKE 'untagged'    
ORDER BY ID_TOPIC DESC

You can't reference a column alias in the WHERE clause - the earliest MySQL supports column alias referencing is the GROUP BY. Use:

  SELECT sm.realName, 
         gl.*,
         x.taglist
    FROM GAME_LEVELS gl
    JOIN SMF_MEMBERS sm ON sm.id_member = gl.id_member
    JOIN (SELECT gt.uuid,
                 GROUP_CONCAT(gt.tag) AS taglist
            FROM GAME_TAGS gt
        GROUP BY gt.uuid) x ON x.uuid = gl.uuid
   WHERE x.taglist LIKE 'untagged'    
ORDER BY ID_TOPIC DESC

回复收藏 0 原文

~没有更多了~