php - 使用 mysql 和 php 对多列进行计数

Question

我有一个包含以下各列的销售表

• 姓名
• 类型(学生、成人、 worker )
• 销售额(A、B、C、D)
• 成本
• 地点(1,2,3,4)

我想获取与名称和按名称分组相关的类型、销售和位置的计数，以便我可以将其显示在表格上

是否可以使用查询获取所有分组值>

任何帮助将不胜感激..

Answer 1

使用GROUP BY，然后使用SUM(condition)来获取计数:

SELECT   name AS Name,
         SUM(type     = 'worker' ) AS Worker,
         SUM(type     = 'student') AS Student,
         SUM(type     = 'adult'  ) AS Adult,
         SUM(sales    = 'A'      ) AS A,
         SUM(sales    = 'B'      ) AS B,
         SUM(sales    = 'C'      ) AS C,
         SUM(sales    = 'D'      ) AS D,
         SUM(location = 1        ) AS Location1,
         SUM(location = 2        ) AS Location2,
         SUM(location = 3        ) AS Location3,
         SUM(location = 4        ) AS Location4
FROM     sales
GROUP BY Name