专家可视化

select b.expertTotal as "专家总数", d.expertAuth as "已实名专家总数"
from (
         (select count(1) as "expertTotal" from experts where is_delete = '0' and status = 1) b,
             (select count(1) as "expertAuth"
              from experts e
                       inner join users u on e.user_id = u.id
              where e.is_delete = '0'
                and e.status = 1
                and u.authentication = true) d
         );

select title_rank as "职级", count(1) as "数量"
from experts
where is_delete = '0'
  and status = 1
group by title_rank;

select  expert_type as "专业类别",count(1) as "数量"
from experts
where is_delete = '0'
  and status = 1
group by expert_type;

select  workplace_type as "单位类别",count(1) as "数量"
from experts
where is_delete = '0'
  and status = 1
group by workplace_type;

select  highest_degree as "学历",count(1) as "数量"
from experts
where is_delete = '0'
  and status = 1
group by highest_degree;

select tmp.expert_name                                  as "专家姓名",
       tmp.taskAuditCount                               as "创客任务评审数",
       tmp.competitionAuditCount                        as "竞赛评审数",
       (tmp.taskAuditCount + tmp.competitionAuditCount) as "创客+竞赛评审总数"
from (select e.expert_name,
             (select COUNT(distinct container_id) from task_expert where expert_id = e.id and container_type = 1  and status in (1,2)) as taskAuditCount,
             (select COUNT(distinct container_id) from task_expert where expert_id = e.id and container_type = 2 and status in (1,2))    as competitionAuditCount
      from experts e
               left join users u on e.user_id = u.id
      where e.is_delete = '0'
        and e.status = 1) tmp
 order by (tmp.taskAuditCount + tmp.competitionAuditCount) desc
limit 0,12;

select DATE_FORMAT(created_on, '%Y') as 'name', count(*) as 'value'
from experts
where is_delete = '0'
  and status = 1
group by DATE_FORMAT(created_on, '%Y')
order by DATE_FORMAT(created_on, '%Y');

select a.domain_value as "name", count(a.domain_value) as "value"
from (select review_area_one as domain_value
      from experts
      where is_delete = '0'
        and status = 1
        and review_area_one is not null
        and review_area_one != ''
      union all
      select review_area_two as domain_value
      from experts
      where is_delete = '0'
        and status = 1
        and review_area_two is not null
        and review_area_two != ''
      union all
      select review_area_three as domain_value
      from experts
      where is_delete = '0'
        and status = 1
        and review_area_three is not null
        and review_area_three != '') a
group by a.domain_value
order by count(a.domain_value) desc
limit 0,9;