mysql - 如果值不同则 SQL group by 留空

Question

sql问题

我有这样的表格:

id | Animal | country | gender | qty
1  | cow    | USA     | male   | 6
2  | cow    | PHP     | female | 8
3  | cow    | USA     | male   | 7
4  | cow    | JPN     | male   | 6
5  | cow    | USA     | female | 5

如何做出一些结果

   | Animal | country | gender | qty
   | cow    | USA     |        | 18 (sumqty for cow from USA)
   | cow    | PHP     | female | 8
   | cow    | JPN     | male   | 6

用词来说，如果动物是相同且相同的国家，则求和(数量)并保留空性别，因为它混合了男性和女性。

我使用这样的代码

SELECT *,sum(qty) as sqty FROM myTable GROUP BY animal,country

但是 echo ['gender'] 时的结果显示了最后一个值(#5)如何让它发挥作用？不，我有很多动物。如鸡、鸭等。

Answer 1

您可以使用 case 语句来执行您想要的操作。当您希望 age 为空时有点不清楚，但是:

select animal, country,
       (case when min(age) = max(age) then min(age) end) as age,
       sum(qty)
from myTable
group by animal, country;