例子一:
有一个表,数据如:
mysql> select * from tid_action;
+----+------+-------------+
| id | tid | action |
+----+------+-------------+
| 1 | 47 | restorePost |
| 2 | 47 | restorePost |
| 3 | 47 | restorePost |
| 4 | 47 | replyTopic |
| 5 | 47 | replyTopic |
| 6 | 47 | deletePost |
| 7 | 48 | replyTopic |
| 8 | 48 | replyTopic |
| 9 | 48 | restorePost |
| 10 | 48 | deletePost |
| 11 | 48 | deletePost |
| 12 | 48 | deletePost |
| 13 | 48 | restorePost |
+----+------+-------------+
13 rows in set (0.01 sec)
现在要按tid 分组统计,每个tid的action类型replyTopic 、restorePost 、deletePost 分别有多少行。
sql语句:
SELECT tid,
sum(CASE WHEN ACTION='replyTopic' THEN 1 ELSE 0 END ) replyCount,
sum(CASE WHEN ACTION='restorePost' THEN 1 ELSE 0 END ) restoreCount,
sum(CASE WHEN ACTION='deletePost' THEN 1 ELSE 0 END ) deleteCount
FROM tid_action
GROUP BY tid
查询结果:
+------+------------+--------------+-------------+
| tid | replyCount | restoreCount | deleteCount |
+------+------------+--------------+-------------+
| 47 | 2 | 3 | 1 |
| 48 | 2 | 2 | 3 |
+------+------------+--------------+-------------+
2 rows in set (0.00 sec)
例子二:
有一个bbs7_activity_item表,主要字段如下,activityitemid是主键,STATUS=1表示审核通过的状态。
现在想创建一个新表,来统计总的人数和审核通过的人数,按照tid分组,简单写法:
CREATE TABLE activity_signUp_censored AS
SELECT tid,sum(persons) signUpCount,
sum(CASE WHEN STATUS = 1 THEN persons ELSE 0 END) censoredCount
FROM bbs7_activity_item GROUP BY tid
经验证,这个sql是正确的。