使用 UNION 获取的数据数组的 MySQL 键名

Question

表对象

id    name    is_animal
-----------------------
1     dog     1
2     cat     1
3     chair   0
4     rabbit  1

查询

(SELECT name AS animal_name FROM objects WHERE is_animal = 1)
UNION
(SELECT name AS object_name FROM objects WHERE is_animal = 0)

结果

[animal_name] => dog
[animal_name] => cat
[animal_name] => rabbit
[animal_name] => chair    // expected: [object_name] => chair

我已经在第二个 SELECT 中使用了 AS，为什么它返回 animal_name 而不是 object_name？

Answer 1

使用 UNION :

(SELECT name AS animal_name,'' object_name FROM objects WHERE is_animal = 1)
UNION
(SELECT '' as animal_name, name AS object_name FROM objects WHERE is_animal = 0)

用大小写:

select case is_animal when 1 name else '' end as  animal_name,
       case is_animal when 0 name else '' end as  object_name
from objects