代码之家  ›  专栏  ›  技术社区  ›  Muneer

如何在WHERE子句中使用MYSQL的“AS”返回值?

  •  1
  • Muneer  · 技术社区  · 15 年前

    SELECT 
       contents.id, contents.title, contents.createdBy,
     (SELECT userGroup FROM suser_profile WHERE userId = 
             (SELECT users.id 
              FROM 
                users 
              WHERE 
                login = contents.createdBy)
        ) as userGroupID
    FROM 
       contents 
    WHERE
       contents.id > 0   
       AND contents.contentType = 'News' 
       **AND userGroupID = 3**
    LIMIT 0, 10
    

    用户组ID 在WHERE子句中,SQL触发一个错误,表示“WHERE子句”中的SQL error(1054):未知列“userGroupID”

    同时,如果我做如下的小改动,,

    SELECT 
       contents.id, contents.title, contents.createdBy
    FROM 
       smart_cms_contents 
    WHERE
       contents.id > 0   
       AND contents.contentType = 'News' 
       **AND (SELECT userGroup FROM user_profile WHERE userId = 
             (SELECT users.id 
              FROM 
                users 
              WHERE 
                users.login = contents.createdBy)
        ) = 3**
    LIMIT 0, 10
    

    然后查询工作正常。

    我必须使用多个 用户组ID

    *注意:表名不是我在项目中使用的原始名称。如果表名中有错误,可以忽略它。我主要关心的是如何使用 作为

    忽略查询中的星星*

    3 回复  |  直到 14 年前
        1
  •  1
  •   Dean Harding    15 年前

    如果我正确理解了您的初始查询,那么我相信您要做的是加入:

    SELECT DISTINCT 
       contents.id, contents.title, contents.createdBy
    FROM
       contents INNER JOIN users
          ON contents.createdBy = users.login
       INNER JOIN user_profile
          ON user_profile.userId = users.id
    WHERE
       contents.id > 0   
       AND contents.contentType = 'News' 
       AND user_profile.userGroupID = 3
    LIMIT 0, 10
    
        2
  •  2
  •   oezi    15 年前

    使用 HAVING . 例子:

    WHERE
        contents.id > 0   
      AND
        contents.contentType = 'News'
    HAVING
        userGroupID = 3
    LIMIT 0, 10
    
        3
  •  1
  •   Ignacio Vazquez-Abrams    15 年前

    SELECT DISTINCT c.id, c.title, c.createdBy, s.userGroup AS userGroupID
    FROM contents AS c
    INNER JOIN users AS u
      ON c.createdBy = u.login
    INNER JOIN suser_profile AS s
      ON s.userId = u.id
    WHERE c.id > 0
      AND c.contentType = 'News'
      AND s.userGroup = 3