mysql - MySQL多次计数

我有下表：

+----+----------+------------+
| id | drink    | gender     |
+----+----------+------------+
|  1 | Beer     | m          |
|  2 | Milk     | f          |
|  3 | Milk     | f          |
|  4 | Tea      | m          |
+----+----------+------------+

现在我想数一下哪个性别更喜欢哪种饮料。结果应该是这样的：

+-------+-------+--------+
| drink | fem   | male   |
+-------+-------+--------+
|  Beer | 0     | 1      |
|  Milk | 2     | 0      |
|  Tea  | 0     | 1      |
+----+----------+--------+

有人知道吗？
提前谢谢你的建议。

最佳答案

SELECT drink,
       SUM(CASE WHEN gender = 'f' THEN 1 ELSE 0 END) fem,
       SUM(CASE WHEN gender = 'm' THEN 1 ELSE 0 END) male
FROM tableName
GROUP BY drink

SQLFiddle Demo
上面的查询可以进一步转换为准备好的语句。这很有帮助，例如，您可以在不修改原始查询的情况下为性别添加另一个值（例如，u表示中性）。例子，

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when gender = ''',
      gender,
      ''' then 1 ELSE 0 end) AS ',
      gender
    )
  ) INTO @sql
FROM tableName;

SET @sql = CONCAT('SELECT drink, ', @sql, '
                   FROM tableName
                   GROUP BY drink');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle Demo

关于mysql - MySQL多次计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13114215/