MYSQL - 如何分组并取非空值

Question

我有一个看起来像这样的表:

Name  | Color | Fruit | Animal
-----------------------------
Steve | Blue  | Null  | Null
Steve | Null  | Apple | Null
Steve | Null  | Null  | Sheep
John  | Red   | Apple | Null
John  | Null  | Null  | Cow

等...

我要它凝结成

Name  | Color | Fruit | Animal
------------------------------
Steve | Blue  | Apple | Sheep
John  | Red   | Apple | Cow

我尝试过的其他分组解决方案在 Null 上效果不佳。

Answer 1

可以使用max和group by

SELECT t.name,
       max(t.color) as color,
       max(t.fruit) as fruit,
       max(t.animal) as animal)
FROM YourTable
GROUP BY t.name