业务需求1:有一张员工信息表,现在进行如下信息统计
查出表中‘中共党员’,‘中共预备党员’,‘发展对象’,‘入党积极分子’,‘入党申请人’的数量。
特殊性是:‘中共党员’,‘中共预备党员’;‘发展对象’,‘入党积极分子’,‘入党申请人’两组是在表中不同的字段。
解决方法:
用Mysql的count(case when poli_look = '中共党员' then '中共党员' end) as '中共党员',
解决:如下
select
count(case when poli_look = '中共党员' then '中共党员' end) as '中共党员',
count(case when poli_look = '中共预备党员' then '中共预备党员' end) as '中共预备党员',
count(case when party_nodes = '发展对象' then '发展对象' end) as '发展对象',
count(case when party_nodes = '入党积极分子' then '入党积极分子' end) as '入党积极分子',
count(case when party_nodes = '入党申请人' then '入党申请人' end) as '入党申请人'
from rost_use
结果:
加条件的查询如下
select
count(case when poli_look = '中共党员' then '中共党员' end) as '中共党员',
count(case when poli_look = '中共预备党员' then '中共预备党员' end) as '中共预备党员',
count(case when party_nodes = '发展对象' then '发展对象' end) as '发展对象',
count(case when party_nodes = '入党积极分子' then '入党积极分子' end) as '入党积极分子',
count(case when party_nodes = '入党申请人' then '入党申请人' end) as '入党申请人'
from rost_use
where part_name = 'xxxxxxxxxx党支部'
业务需求2有一种表记录如下信息
现在表中的数据是有些记录的是多个类型,用【;】隔开,现在要统计的是每个类型出现多少次,有多个类型的分开到各个类型里面。如图
代码如下
SELECT `name` meeting_type,count(*) num FROM (
select case when meeting_type like '%党小组会%' then '党小组会' end as `name` from party_conference where meeting_type like '%党小组会%'
union all
select case when meeting_type like '%党小组会%' then '党小组会' end as `name` from party_conference where meeting_type like '%党小组会%'
union all
select case when meeting_type like '%民主评议党员%' then '民主评议党员' end as `name` from party_conference where meeting_type like'%民主评议党员%'
union all
select case when meeting_type like '%主题党日%' then '主题党日' end as `name` from party_conference where meeting_type like '%主题党日%'
union all
select case when meeting_type like '%支部党课%' then '支部党课' end as `name` from party_conference where meeting_type like '%支部党课%'
union all
select case when meeting_type like '%组织生活会%' then '组织生活会' end as `name` from party_conference where meeting_type like '%组织生活会%'
union all
select case when meeting_type like '%支部党员大会%' then '支部党员大会' end as `name` from party_conference where meeting_type like '%支部党员大会%'
union all
select case when meeting_type like '%党委/党总支/支委会%' then '党委/党总支/支委会' end as `name` from party_conference where meeting_type like '%党委/党总支/支委会%') tmp GROUP BY `name`