如何查询数据并根据两个字段分行分列统计数目，类似于 Excel 的数据透视表。例如，下面的表格card_record中，以fund_id为行，card_id为列，统计每个用户获得的各类卡片分别有多少张（条）。
+-------+----------+--------------+-----------+------------+
| id | fund_id | fund_ticket | card_id | card_valid |
+-------+----------+--------------+-----------+------------+
| 53460 | 10334728 | 2fcd971b64e8 | spade-a | 0 |
| 53461 | 10289485 | 9f8bd96e29b1 | diamond-j | 0 |
| 53462 | 10283656 | 388c9299c4f1 | diamond-a | 0 |
| 53463 | 10292558 | e45a7950e7df | heart-9 | 0 |
| 53464 | 10397050 | f01afb969161 | diamond-q | 0 |
| 53467 | 10289485 | 860a2af9a0eb | heart-5 | 1 |
| 53468 | 10289733 | 8fd69ba02eb1 | spade-9 | 1 |
| 53469 | 10415694 | 627ee321acf6 | diamond-j | 1 |
| 53470 | 10415694 | 627ee321acf6 | heart-8 | 1 |
| 53471 | 10415694 | 627ee321acf6 | spade-2 | 1 |
| 53472 | 10415694 | 627ee321acf6 | spade-5 | 1 |
| 53473 | 10415694 | 627ee321acf6 | diamond-j | 1 |
| 53474 | 10415694 | 627ee321acf6 | club-k | 1 |
| 53475 | 10415694 | 627ee321acf6 | heart-5 | 1 |
| 53476 | 10415694 | 627ee321acf6 | club-2 | 1 |
| 53477 | 10415694 | 627ee321acf6 | club-8 | 1 |
| 53478 | 10415694 | 627ee321acf6 | heart-7 | 1 |
| 53479 | 10289485 | 82234ac58d6c | spade-3 | 1 |
| 53480 | 10290318 | 42436ad24646 | heart-a | 1 |
| 53481 | 10297269 | 1101329eb34e | diamond-q | 1 |
+-------+----------+--------------+-----------+------------+
要实现所述功能，需要在查询的结果中判断对应card_id的值是哪一列，然后再通过AS命名该列，如下面的静态版本所示
SELECT fund_id, fund_ticket,
COUNT(CASE WHEN card_id = 'spade-a' THEN fund_id END) AS `spade-a`,
COUNT(CASE WHEN card_id = 'diamond-j' THEN fund_id END) AS `diamond-j`,
COUNT(CASE WHEN card_id = 'diamond-a' THEN fund_id END) AS `diamond-a`
FROM card_record
GROUP BY fund_id;
得到的查询结果是
+----------+--------------+---------+-----------+-----------+
| fund_id | fund_ticket | spade-a | diamond-j | diamond-a |
+----------+--------------+---------+-----------+-----------+
| 10283656 | 388c9299c4f1 | 0 | 0 | 1 |
| 10289485 | 9f8bd96e29b1 | 0 | 1 | 0 |
| 10289733 | 8fd69ba02eb1 | 0 | 0 | 0 |
| 10290318 | 42436ad24646 | 0 | 0 | 0 |
| 10292558 | e45a7950e7df | 0 | 0 | 0 |
| 10297269 | 1101329eb34e | 0 | 0 | 0 |
| 10334728 | 2fcd971b64e8 | 1 | 0 | 0 |
| 10397050 | f01afb969161 | 0 | 0 | 0 |
| 10415694 | 627ee321acf6 | 0 | 2 | 0 |
+----------+--------------+---------+-----------+-----------+
静态版本有其局限性，当目标列（也就是card_id）类型众多时，不可能手动将条件一条一条列出，因此需要利用MySQL的CONCAT等函数构造语句，然后再执行构造完成的所有列条件的语句，如下面的动态版本
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'COUNT(CASE WHEN card_id = ''',card_id,''' THEN fund_id END) AS `',card_id,'`'
)
) INTO @sql
FROM card_record;
SET @sql = CONCAT('SELECT fund_id, fund_ticket, ',@sql,'
FROM card_record
GROUP BY fund_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
''',card_id,'''的目的是遍历card_id变量的值，作为所有条件判断的内容，''两个单引号转义为字符串内的单引号CASE WHEN ... THEN ... (ELSE ...) END，若上述代码中不写COUNT，那么输出的结果则是THEN后响应的fund_id的结果`',card_id,'`两边设置反引号的作用是声明列名，避免字段的Unicode字符解析错误SELECT）与第3条语句（SET）查询的表、WHERE条件应当保持一致注意，在使用中可能出现如下的提示
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM card_record GROUP BY fund_id' at line 2
这是因为默认GROUP_CONCAT构造的语句最大长度为1024，当条件过多时语句会被截断到最大长度，在语句前用下面的代码声明最大长度为一个较大的值即可解决
SET SESSION group_concat_max_len = 10000;
得到的查询结果是
+----------+--------------+--------+--------+--------+-----------+-----------+-----------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| fund_id | fund_ticket | club-2 | club-8 | club-k | diamond-a | diamond-j | diamond-q | heart-5 | heart-7 | heart-8 | heart-9 | heart-a | spade-2 | spade-3 | spade-5 | spade-9 | spade-a |
+----------+--------------+--------+--------+--------+-----------+-----------+-----------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| 10283656 | 388c9299c4f1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 10289485 | 9f8bd96e29b1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| 10289733 | 8fd69ba02eb1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| 10290318 | 42436ad24646 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 10292558 | e45a7950e7df | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 10297269 | 1101329eb34e | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 10334728 | 2fcd971b64e8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 10397050 | f01afb969161 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 10415694 | 627ee321acf6 | 1 | 1 | 1 | 0 | 2 | 0 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
+----------+--------------+--------+--------+--------+-----------+-----------+-----------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
https://www.jb51.net/article/167403.htm