简单case when用法

例子一：

有一个表，数据如：

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是正确的。