DB2学习
1.为了使用ORG_ID，由于OCRM_F_MM_MKT_PLAN a 没有ORG_ID，但是OCRM_F_MM_EXECUTE_INST_DESC d 表里面有，可以使a表连接b表，创建临时表t。
select t.PLAN_S AS MARKET_PLAN_COUNT,t.SUM_PLAN AS PLAN_ACC_COUNT
from (select count(a.MKT_PLAN_STAT) as PLAN_S,sum(case when a.MKT_PLAN_STAT='3' then 1 else 0 end) as SUM_PLAN from OCRM_F_MM_MKT_PLAN a left join OCRM_F_MM_EXECUTE_INST_DESC d on (d.PLAN_ID=a.PLAN_ID))t;
2.
select f.ORG_ID,f.ORG_NAME,t.MARKET_PLAN_COUNT,t.PLAN_ACC_PER,t.PLAN_ACC_COUNT,
b.MARKET_ACT_COUNT,b.ACT_ACC_COUNT,b.ACT_ACC_PER,c.OPPOR_COUNT,
c.OPPOR_ACC_COUNT,c.OPPOR_ACC_PER
from( select d.org_id,count(a.MKT_PLAN_STAT) as MARKET_PLAN_COUNT,
sum(case when a.MKT_PLAN_STAT='3' then 1 else 0 end) as PLAN_ACC_COUNT,
ROUND(CAST(sum(case when a.MKT_PLAN_STAT='3' then 1 else 0 end) AS DOUBLE) / count(a.MKT_PLAN_STAT)*100,4) as PLAN_ACC_PER
from OCRM_F_MM_MKT_PLAN a left join OCRM_F_MM_EXECUTE_INST_DESC d on (d.PLAN_ID=a.PLAN_ID) group by org_id)t
left join (select CREATE_ORG,count( MKT_ACTI_STAT) as MARKET_ACT_COUNT,sum(case when MKT_ACTI_STAT='4' then 1 else 0 end) AS ACT_ACC_COUNT,
ROUND( sum(case when MKT_ACTI_STAT='4' then 1 else 0 end)/count( MKT_ACTI_STAT)*100,4) as ACT_ACC_PER
from OCRM_F_MK_MKT_ACTIVITY group by CREATE_ORG) b on (t.ORG_ID=b.CREATE_ORG)
left join
(
select CREATE_ORG_ID, count(OPPOR_STAT) as OPPOR_COUNT, sum(case when OPPOR_STAT=7 then 1 else 0 end) + sum(case when OPPOR_STAT= 8 then 1 else 0 end) AS OPPOR_ACC_COUNT,
ROUND(CAST( ( sum(case when OPPOR_STAT=7 then 1 else 0 end) + sum(case when OPPOR_STAT= 8 then 1 else 0 end)) AS DOUBLE)/count(OPPOR_STAT) *100,4)as OPPOR_ACC_PER from OCRM_F_MM_MKT_BUSI_OPPOR group by CREATE_ORG_ID) c on (b.CREATE_ORG=c.CREATE_ORG_ID)
left join ADMIN_AUTH_ORG f on (c.CREATE_ORG_ID=f.ORG_ID)
3.统计状态为3，3代表成功的，把3看做1，然后累加1，最后得到表里面有多少个3
select count(a.MKT_PLAN_STAT) as MARKET_PLAN_COUNT, sum(case when a.MKT_PLAN_STAT='3' then 1 else 0 end) as PLAN_ACC_COUNT from OCRM_F_MM_MKT_PLAN a;
4.统计查找客户经理名称，编号，这个客户经理营销计划数量，完成数量，完成率以及营销活动数量，完成数量，完成率以及商机数量，完成数量，完成率，以及这个客户经理的所属机构，机构号。
select
f.CUST_MANAGER_ID,f.CUST_MANAGER_NAME,
t1.MARKET_PLAN_COUNT,t1.PLAN_ACC_COUNT,t1.PLAN_ACC_PER,
t2.MARKET_ACT_COUNT,t2.ACT_ACC_COUNT,t2.ACT_ACC_PER,
t3.OPPOR_COUNT,t3.OPPOR_ACC_COUNT,t3.OPPOR_ACC_PER,
g.ORG_ID,g.ORG_NAME
from (
SELECT
b.EXECUTOR,
COUNT(a.MKT_PLAN_STAT) AS MARKET_PLAN_COUNT,
SUM(
CASE
WHEN a.MKT_PLAN_STAT='3'
THEN 1
ELSE 0
END) AS PLAN_ACC_COUNT,
ROUND( CAST(SUM(
CASE
WHEN a.MKT_PLAN_STAT='3'
THEN 1
ELSE 0
END) AS DOUBLE) / COUNT(a.MKT_PLAN_STAT)*100,4) AS PLAN_ACC_PER
FROM
OCRM_F_MM_MKT_PLAN a
LEFT JOIN OCRM_F_MM_PLAN_CUST b
ON
(
b.PLAN_ID=a.PLAN_ID
)
GROUP BY
EXECUTOR ) t1
left join
( SELECT
d.HOST_MGR_ID,
COUNT( MKT_ACTI_STAT) AS MARKET_ACT_COUNT,
SUM(
CASE
WHEN MKT_ACTI_STAT='4'
THEN 1
ELSE 0
END) AS ACT_ACC_COUNT,
ROUND( SUM(
CASE
WHEN MKT_ACTI_STAT='4'
THEN 1
ELSE 0
END)/COUNT( MKT_ACTI_STAT)*100,4) AS ACT_ACC_PER
FROM
OCRM_F_MK_MKT_ACTIVITY c left join OCRM_F_MK_ACTI_CUSTOMER d on(c.MKT_ACTI_ID=d.MKT_ACTI_ID)
GROUP BY
d.HOST_MGR_ID) t2 on (t1.EXECUTOR=t2.HOST_MGR_ID)
left join
( SELECT
e.CREATER_ID,
COUNT(OPPOR_STAT) AS OPPOR_COUNT,
SUM(
CASE
WHEN OPPOR_STAT=7
THEN 1
ELSE 0
END) + SUM(
CASE
WHEN OPPOR_STAT= 8
THEN 1
ELSE 0
END) AS OPPOR_ACC_COUNT,
ROUND(CAST( ( SUM(
CASE
WHEN OPPOR_STAT=7
THEN 1
ELSE 0
END) + SUM(
CASE
WHEN OPPOR_STAT= 8
THEN 1
ELSE 0
END)) AS DOUBLE)/COUNT(OPPOR_STAT) *100,4)AS OPPOR_ACC_PER
FROM
OCRM_F_MM_MKT_BUSI_OPPOR e
GROUP BY
e.CREATER_ID) t3 on (t2.HOST_MGR_ID=t3.CREATER_ID)
left join
OCRM_F_CM_CUST_MGR_INFO f on (t3.CREATER_ID=f.CUST_MANAGER_ID)
left join ADMIN_AUTH_ORG g on (f.AFFI_INST_ID=g.ORG_ID)
