我如何才能找到某一建筑中男女比例？

Question

表：

- Activities: id, userId, description, buildingId
- Buildings: id, name
- User: id, name, gender

用户可以创建活动，例如：“检查邮件”将是一个活动，buildingid链接到正在进行特定活动的建筑物。

我需要的是一个查询，将得到的百分比男性/女性每座建筑，基于数量的活动在这样的建筑。

例如：

User: 1, mike
Building: 1, main hall

Activities:

id: 1
userId: 1 (mike)
description: "checking mail"
buildingId: 1 (main hall)

id: 2
userId: 1 (mike)
description: "getting coffee"
buildingId: 1 (main hall)

所以我想要得到1号楼(主厅)的%女性/男性，我该怎么做才是最好的方法？

Answer 1

SELECT buildingId, 
  (SUM(CASE WHEN `User`.gender='male' THEN 1 ELSE 0 END)/
  COUNT(*))*100 as male_percent
FROM Activities JOIN `User` ON Activities.userID=`User`.id 
GROUP BY `Activities`.buildingId

或者，如果您更喜欢比率：

SELECT buildingId, 
  SUM(CASE WHEN `User`.gender='male' THEN 1 ELSE 0 END)/
  SUM(CASE WHEN `User`.gender='female' THEN 1 ELSE 0 END) as ratio
FROM Activities JOIN `User` ON Activities.userID=`User`.id 
GROUP BY `Activities`.buildingId