mysql 统计分组 语句

Question

id   name    code_id     code_name   is_true    score
1    张三        1          帅气        是        5
2    张三        2          阔气        是        4
3    张三        1          帅气        是        4
4    张三        2          阔气        否        3        
5    李四        1          帅气        是        5
6    李四        2          阔气        是        3
mysql 如何 按name 分组 统计：【帅气】 有多少个“是”和“否”，【阔气】有多少个“是”和“否”，还有多少个【score】语句怎样写      

Answer 1

先猜一下。select name,
sum(if(code_name='帅气' and is_true='是',1,0)),
sum(if(code_name='帅气' and is_true='否',1,0)),
sum(if(code_name='阔气' and is_true='是',1,0)),
sum(if(code_name='阔气' and is_true='否',1,0)),
count(*)
from table1
group by name

Answer 2

估计是样mysql> select   name,
    ->          sum(if(code_name='帅气' and is_true='是',1,0)),
    ->          sum(if(code_name='帅气' and is_true='否',1,0)),
    ->          sum(if(code_name='阔气' and is_true='是',1,0)),
    ->          sum(if(code_name='阔气' and is_true='否',1,0)),
    ->          count(*)
    -> from test
    -> group by name;
+------+------------------------------------------------+------------------------------------------------+------------------------------------------------+------------------------------------------------+----------+
| name | sum(if(code_name='帅气' and is_true='是',1,0)) | sum(if(code_name='帅气' and is_true='否',1,0)) | sum(if(code_name='阔气' and is_true='是',1,0)) | sum(if(code_name='阔气' and is_true='否',1,0)) | count(*) |
+------+------------------------------------------------+------------------------------------------------+------------------------------------------------+------------------------------------------------+----------+
| 李四 |                                              1 |                                              0 |                                              1 |                                              0 |        2 |
| 张三 |                                              2 |                                              0 |                                              1 |                                              1 |        4 |
+------+------------------------------------------------+------------------------------------------------+------------------------------------------------+------------------------------------------------+----------+
2 rows in set (0.00 sec)