- 论坛徽章:
- 93
|
回复 1# miaaa
可以参考一下:
- mysql> select * from tb;
- +------+---------+----------+----------+
- | Name | Feature | Category | hardware |
- +------+---------+----------+----------+
- | aaa | M | X | 111 |
- | bbb | M | Y | 111 |
- | ccc | M | X | 111 |
- | ddd | N | Y | 111 |
- | eee | N | Y | 111 |
- | fff | N | X | 222 |
- +------+---------+----------+----------+
- 6 rows in set
- mysql> select Feature, hardware, sum(if(Category = 'X', 1, 0)) as 'X', sum(if(Category = 'Y', 1, 0)) as 'Y'
- from tb
- group by Feature, hardware;
- +---------+----------+---+---+
- | Feature | hardware | X | Y |
- +---------+----------+---+---+
- | M | 111 | 2 | 1 |
- | N | 111 | 0 | 2 |
- | N | 222 | 1 | 0 |
- +---------+----------+---+---+
- 3 rows in set
- mysql> select ifnull(Feature, "") as "Feature", ifnull(hardware, "") as "hardware", sum(if(Category = 'X', 1, 0)) as 'X', sum(if(Category = 'Y', 1, 0)) as 'Y'
- from tb
- group by Feature, hardware
- with rollup;
- +---------+----------+---+---+
- | Feature | hardware | X | Y |
- +---------+----------+---+---+
- | M | 111 | 2 | 1 |
- | M | | 2 | 1 |
- | N | 111 | 0 | 2 |
- | N | 222 | 1 | 0 |
- | N | | 1 | 2 |
- | | | 3 | 3 |
- +---------+----------+---+---+
- 6 rows in set
复制代码
|
|