计算列上的 MySQL IF 语句

Question

如何在“Where”子句中使用“IF”语句的 SQL 语句中使用计算列。

我的查询在下面。我不断收到错误

“where 子句”中存在未知列“e_owner_id_church”

SELECT idu,fname,lname,id_file,friendly_name,file_name,uploaded_by,date_uploaded,
file_last_modified,owner_id, 
CASE WHEN owned_by='church' THEN church_name WHEN owned_by='user' THEN CONCAT(fname, ' ', lname) END AS e_owner_name, 
CASE WHEN owned_by='church' THEN id_church END AS e_owner_id_church,
CASE WHEN owned_by='user' THEN idu END AS e_owner_id_user 
FROM files,file_affiliations,churches,users 
WHERE owner_id=IF(e_owner_id_church != null, e_owner_id_church, e_owner_id_user) 
GROUP BY file_id

Answer 1

这可以作为使用 HAVING 子句的解决方法:

SELECT idu
     , fname
     , lname
     , id_file
     , friendly_name
     , file_name
     , uploaded_by
     , date_uploaded
     , file_last_modified
     , owner_id
     , CASE owned_by
        WHEN 'church' THEN church_name
        WHEN 'user' THEN CONCAT(fname, ' ', lname)
       END AS e_owner_name
     , CASE owned_by
        WHEN 'church' THEN id_church
        ELSE NULL
       END AS e_owner_id_church
     , CASE owned_by
        WHEN 'user' THEN idu
        ELSE NULL
       END AS e_owner_id_user
FROM files
   , file_affiliations
   , churches
   , users 
GROUP BY file_id
HAVING owner_id = IFNULL(e_owner_id_church, e_owner_id_user)

我想警告您，此查询不是标准 SQL，事实上，它选择的字段既不包含在 GROUP BY 子句中，也不包含在结果中组函数。

最后，我认为上面的内容可以简化如下:

SELECT idu
     , fname
     , lname
     , id_file
     , friendly_name
     , file_name
     , uploaded_by
     , date_uploaded
     , file_last_modified
     , owner_id
     , CASE owned_by
        WHEN 'church' THEN church_name
        WHEN 'user' THEN CONCAT(fname, ' ', lname)
        ELSE NULL
       END AS e_owner_name
     , CASE owned_by
        WHEN 'church' THEN id_church
        WHEN 'user' THEN idu
       END AS e_owner_id
FROM files
   , file_affiliations
   , churches
   , users 
GROUP BY file_id
HAVING owner_id = e_owner_id