将mysql表中的记录计数为不同的列，具体取决于列的不同值 [英] Count records in mysql table as different columns depending on different values of a column

问题描述

请考虑以下示例表结构：

Please consider following sample table structure:

+-------------------------------+--------------+------+-----+---------+-------+
| Field                         | Type         | Null | Key | Default | Extra |
+-------------------------------+--------------+------+-----+---------+-------+
| msisdn                        | varchar(20)  | NO   | PRI | NULL    |       |
| job_search_text               | varchar(500) | YES  |     | NULL    |       |
| local_job_region_id           | int(11)      | YES  | MUL | NULL    |       |
|   .......                     | .......      | ...  | ... | ....    | ....  |
|   .......                     | .......      | ...  | ... | ....    | ....  |
|   .......                     | .......      | ...  | ... | ....    | ....  |
|   .......                     | .......      | ...  | ... | ....    | ....  |
|   .......                     | .......      | ...  | ... | ....    | ....  |
|   .......                     | .......      | ...  | ... | ....    | ....  |
+-------------------------------+--------------+------+-----+---------+-------+

我想对行计数如下：

If job_search_text='a' then count row as 'A'
Else If job_search_text='b' then count row as 'B'
Else If job_search_text='c' then count row as 'C'
.
.
.
Else count row as 'Other'

我写的查询是： p>

The query which I wrote is like:

SELECT COUNT(CASE WHEN job_search_text='a' THEN 1 END) as 'A',COUNT(CASE WHEN job_search_text='b' THEN 1 END) as 'b' FROM subscriber 

但是这个查询比较每个CASE的job_search_text，它不处理上面提到的Else情况。请帮助我。

But this query compares job_search_text for every CASE, and also it does not handle the Else case mentioned above. Please help me.

推荐答案

你在正确的轨道，但改变计数到SUM像

You were on the right track, but change count to SUM( IF( ))... something like

select
      sum( if( s.job_search_text = 'a', 1, 0 ) ) as 'A',
      sum( if( s.job_search_text = 'b', 1, 0 ) ) as 'B',
      sum( if( s.job_search_text = 'c', 1, 0 ) ) as 'C',
      sum( if( s.job_search_text = 'd', 1, 0 ) ) as 'D',
      sum( if( s.job_search_text = 'e', 1, 0 ) ) as 'E',
      sum( if( s.job_search_text IN ( 'a', 'b', 'c', 'd', 'e' ), 0, 1 ) ) as 'Other'
   from 
      subscriber s

找到已经考虑的东西，那么它总计为零值。如果CANT找到一个a-e条目，它会为您的其他计数总和。

The "IN" test for the other, if it FINDs something already accounted for, then it's summing a zero value. If it CANT find one of the a-e entries, it sums the ONE for you "Other" count.

这篇关于将mysql表中的记录计数为不同的列，具体取决于列的不同值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！