mysql - 如何将列名打印为mysql中的行数？

Question

我有下表

opposition    |   dismissals
--------------+-------------
Australia     |   lbw
South Africa  |   run_out
England       |   bowled
Australia     |   run_out
England       |   lbw

我想按如下方式打印输出

Opposition    |  lbw  | run_out  | bowled
--------------+-------+----------+-------
Australia     |  1    | 1        | 0
England       |  1    | 0        | 1
South Africa  |  0    | 1        | 0

我无法弄清楚如何使用行值作为列名和打印相应的计数。任何人都可以在mysql中建议如何做到这一点？我现在被这个问题困扰了好几天。甚至尝试谷歌搜索，但我找不到任何东西。

标签： mysqlsqldatabasepivotaggregate-functions

Answer 1

使用条件聚合。在 MySQL 中：

select opposition,
    sum(dismissals = 'lbw'    ) lbw,
    sum(dismissals = 'run_out') run_out,
    sum(dismissals = 'bowled' ) bowled
from mytable
group by opposition