mysql - 如果值为空，则添加一列

Question

我想根据表中的字段 field_value 是否为 NULL 向结果集中添加一列。

这是我有效的查询:

SELECT field_id, (
   SELECT field_value
   FROM employee_field_info
   WHERE employee_id = '1'
   AND field_id = persona_fields.field_id
) AS field_value, ( 
   SELECT field
   FROM FIELDS WHERE id = persona_fields.field_id
) AS field_name
FROM persona_fields
WHERE persona_id = '1'

如果 field_value 为 NULL，我希望能够添加一个 tinyint(对于 bool)列。

我试过这个:

SELECT field_id, (
   SELECT field_value
   FROM employee_field_info
   WHERE employee_id = '1'
   AND field_id = persona_fields.field_id
) AS field_value, ( 
   SELECT field
   FROM FIELDS WHERE id = persona_fields.field_id
) AS field_name,
IF(field_value IS NULL, "0","1") AS value_bool
FROM persona_fields
WHERE persona_id = '1'

但这只是在“字段列表”中出现“未知列‘field_value’”。无论如何我可以完成我想做的事情，还是我很不走运？

Answer 1

您不能从子选择中引用父 SELECT 中的列。子选择基本上是孤立运行的。您需要做的是将其重写为 JOIN。你的问题很复杂，很难理解，我认为不完整，所以我只能近似解决。

这是一个例子:

SELECT field_id,
  employee_field_info.field_value,
  IF(employee_field_info.field_value IS NULL, "0","1") AS value_bool
  FROM persona_fields
  LEFT JOIN employee_field_info ON employee_field_info.field_id=persona_fields.field_id
  WHERE employee_id=1