MySQL 按列分组并设置变量(如果一行包含特定值)

Question

假设我有下表:

Type        |Ownership         
-------------------------
Drill       | owned
Drill       | owned
Plant       | owned
Plant       | rental
Light Plant | owned
Light Plant | owned
Light Plant | owned
Light Plant | rental

我只想按类型对这些进行分组，如果类型的单个记录包含所有权=“出租”，则将所有权显示为“出租”

这就是我想要的结果:

Type        |Ownership         
-------------------------
Drill       | owned
Plant       | rental
Light Plant | rental

我做了什么:

SELECT Type, Ownership
FROM table
GROUP BY Type, Ownership

当然，这会导致获取某个类型的多个记录(如果该类型有两种所有权)

Answer 1

演示:http://rextester.com/EAAGP39848

SELECT Type, IF(SUM(Ownership = 'rental'), 'rental', 'owned') AS Ownership2
  FROM type_ownership
  GROUP BY Type;

灵感来自:https://stackoverflow.com/a/30285287/1435132

编辑(解释):

SELECT *, Ownership = 'rental' AS isRental FROM type_ownership;

当所有权 = 租金时，isRental 为 1

SELECT Type, SUM(Ownership = 'rental') AS sumRental, IF(SUM(Ownership = 'rental'), 'rental', 'owned') AS Ownership2
FROM type_ownership
GROUP BY Type;

此处 sumRental 是该类型所有权为租赁的记录计数。

因此，如果总和大于 0，即任何所有权为出租。