表格A
+----+------+------+
| id | name | age |
+----+------+------+
| 8 | aaaa | 1 |
| 9 | aaaa | 1 |
| 10 | aaaa | 1 |
| 11 | aaaa | 2 |
| 12 | aaaa | 2 |
| 13 | aaaa | 2 |
| 14 | aaaa | 2 |
| 15 | aaaa | 2 |
| 16 | bbbb | 2 |
| 17 | bbbb | 2 |
| 18 | bbbb | 2 |
| 19 | bbbb | 1 |
| 20 | bbbb | 1 |
| 21 | bbbb | 1 |
| 22 | bbbb | 1 |
+----+------+------+
表格B
+------+-----------------------------------------+----------------------------------------+
| name | sum(case age when 1 then 1 else 0 end ) | sum(case age when 2 then 1 else 0 end) |
+------+-----------------------------------------+----------------------------------------+
| aaaa | 3 | 5 |
| bbbb | 4 | 3 |
+------+-----------------------------------------+----------------------------------------+
select name sum(case age when 1 then 1 else 0 end),sum(case age 2 then 1 else 0 end) from t1;
总结:
当需要统计某个表格数据数量的时候,并不一定用count 还可以巧妙的利用case when then end 来实现
当然也可以使用关联查询