需求:
目前在做一个干部相关的项目,然后需要一个汇总的sql,要求当不输入组织的时候展示所有地市的信息,输入地市信息之后展示地市信息下面的所有干部信息,干部的信息也要有要求,要求展示在刚才超过6年和9年的。
处理:
首先找到需要处理的表,leader_list这是我自己新建的领导的相关的表,然后,应该是要是用函数GROUP BY,取分离城市信息,而且肯定有对应的子查询,因为GROUP BY之后显示需要做限制,我要展示的内容肯定要比GROUP BY后面的内容要多,g_organization组织表,因为业务表中的组织id是可信的,但是组织名称真的五五开,没有必要要冒着这个风险,不如直接多关联一张表。
select leaders.*
from (SELECT city_id as organization_id,
city_name as organization_name,
COUNT(*) AS leadernumber,
SUM(CASE WHEN zf_position_times >= 6 THEN 1 ELSE 0 END) AS zf_position_times6,
SUM(CASE WHEN zf_position_times >= 9 THEN 1 ELSE 0 END) AS zf_position_times9
FROM (select city_id, city_name, zf_position_times
from leader.leader_list
where status = 0 and jd_status = '1') leader
GROUP BY city_id, city_name) leaders
left join dhr.g_organization on g_organization.ORGANIZATION_ID = leaders.organization_id
order by org_order_number
应该是这么写,然后SUM用来计算求和的数。
然后上面的是不穿组织的情况下,也就是只展示地市,也就是组织里面几乎是最外面的一层,但是你在选择了地市了之后需要展示后面地市详情中的信息,其实这里我是讨了一个巧,领导的个人信息中是一个具体的到部门的组织信息,因为这边主打一个安全,口口相传,所以字段我几乎是全部继承,我这边多用了一个字段,company_id
select leaders.*
from (SELECT company_id as organization_id,
company_name AS organization_name,
COUNT(*) AS leadernumber,
SUM(CASE WHEN zf_position_times >= 6 THEN 1 ELSE 0 END) AS zf_position_times6,
SUM(CASE WHEN zf_position_times >= 8 THEN 1 ELSE 0 END) AS zf_position_times9
FROM (select city_id, company_id, company_name, zf_position_times
from leader.leader_list
where city_id = '36065'
and status = 0
and jd_status = '1') leader
GROUP BY company_id, company_name, city_id
having city_id = '36065'
and company_name is not null) leaders
left join dhr.g_organization on g_organization.ORGANIZATION_ID = leaders.organization_id
order by org_order_number
两个sql查询几乎很像,但是我还是废了蛮多的功夫的,我的sql太差啦