mysql - 在MySQL中按国家/地区名称分组

我有一个具有以下字段的表：

id, country_id, name, n_ocurrences
1   uk          John     3
2   us          John     4
3   uk          Matt     0
4   us          Matt     5

我将如何获得一个结果列表，如下所示：

name   uk    us   total_ocurrences
John   3     4            7
Matt   0     5            5

现在我正在用直接的PHP处理结果，但我想知道我是否可以在MySQL中这样做。
编辑：注意这个表比这个大，实际上我正在做一个WHERE，上面有一个国家的id列表。
谢谢

最佳答案

这种类型的数据转换是一个轴心点。在MySQL中，要生成此结果，您将使用一个带case表达式的聚合函数：

select name,
  sum(case when country_id = 'uk' then n_ocurrences else 0 end) occurrences_uk,
  sum(case when country_id = 'us' then n_ocurrences else 0 end) occurrences_us,
  sum(n_ocurrences) total_ocurrences
from yourtable
group by name

见SQL Fiddle with Demo。
如果您提前知道country_id的值，那么上面的版本工作得很好，但是如果您不知道，那么您可以使用准备好的语句生成动态sql：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when country_id = ''',
      country_id,
      ''' then n_ocurrences end) AS occurrences_',
      country_id
    )
  ) INTO @sql
FROM yourtable;

SET @sql = CONCAT('SELECT name, ', @sql, ' ,
                      sum(n_ocurrences) total_ocurrences
                  FROM yourtable
                  GROUP BY name');

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

见SQL Fiddle with Demo
两者都给出了结果：

| NAME | OCCURRENCES_UK | OCCURRENCES_US | TOTAL_OCURRENCES |
-------------------------------------------------------------
| John |              3 |              4 |                7 |
| Matt |              0 |              5 |                5 |