mysql - 计算 mysql 中的分组值

Question

我有两个表:

顾客

id      name
1       John
2       Kevin

客户属性

id      customer_id    code              value       
1       1              main_course                   
2       1              main_course                   
3       1              drink                         
4       1              drink                         
5       1              cash              100         

我想计算每个客户订购的类(class)和饮料。我可以通过这个查询来计算饮料数量:

SELECT
  c.name,
  COUNT(DISTINCT cp.id) AS drinks_count
FROM customer AS c
  JOIN customer_property AS cp ON c.id=cp.customer_id
WHERE cp.code="drink"
  GROUP BY cp.id

这很好用。我的问题 - 是否可以计算同一查询中的类(class)？

Answer 1

您可以为此使用条件求和，并且按 cp.id 进行分组会为您提供该表中的行数，您可以将其作为 c.name

SELECT
  c.name,
  sum(cp.code="drink") AS drinks_count,
  sum(cp.code="main_course") AS main_course_count,
FROM customer AS c
  JOIN customer_property AS cp ON c.id=cp.customer_id
  GROUP BY c.name