贴两个例子:
由左图得到右图,可用如下sql:
- select (case
- when chnl_type_id = '100' then
- '自办营业厅'
- when chnl_type_id = '200' then
- '合作厅'
- when chnl_type_id = '300' then
- '外呼'
- when chnl_type_id = '1' then
- '10086人工'
- when chnl_type_id = '2' then
- '10086自动'
- when chnl_type_id = '6' then
- '短信营业厅'
- when chnl_type_id = '5' then
- '网上营业厅'
- when chnl_type_id = '3' then
- 'WAP营业厅'
- when chnl_type_id = '4' then
- '自助终端'
- else
- ''
- end) as chnl_type_id,
- sum(biz_accept_cnt) as cnt1
- from masacb.tb_cb_rs_echnl_key_serv_m t
- where chnl_type_id in ('1', '2', '3', '4', '5', '6', '100', '200', '300')
- and t.statis_month between 200801 and 200810
- and t.area_id like '%'
- and t.brand_id like '%'
- and t.cust_type_id like '%'
- and t.cust_lvl_id like '%'
- and t.chg_mode like '1'
- and t.biz_accept_type_id like '%'
- and t.serv_type_id like '%'
- group by t.chnl_type_id
例子2:
- select CUST_LVL_ID,
- --biz_accept_type_id,
- sum(case
- when chnl_type_id = '100' then
- cnt1
- end) as 自办营业厅,
- sum(case
- when chnl_type_id = '200' then
- cnt1
- end) as 合作厅,
- sum(case
- when chnl_type_id = '300' then
- cnt1
- end) as 外呼,
- sum(case
- when chnl_type_id = '1' then
- cnt1
- end) as 人工,
- sum(case
- when chnl_type_id = '2' then
- cnt1
- end) as 自动,
- sum(case
- when chnl_type_id = '3' then
- cnt1
- end) as WAP营业厅,
- sum(case
- when chnl_type_id = '4' then
- cnt1
- end) as 自助终端,
- sum(case
- when chnl_type_id = '5' then
- cnt1
- end) as 网上营业厅,
- sum(case
- when chnl_type_id = '6' then
- cnt1
- end) as WAP营业厅
- from (select (case
- when CUST_LVL_ID = '1' then
- '0元'
- when CUST_LVL_ID = '2' then
- '0-50元'
- when CUST_LVL_ID = '3' then
- '50-100元'
- when CUST_LVL_ID = '4' then
- '100-150元'
- when CUST_LVL_ID = '5' then
- '150-200元'
- when CUST_LVL_ID = '6' then
- '200元以上'
- else
- ''
- end) as CUST_LVL_ID,
- chnl_type_id,
- sum(biz_accept_succ_cnt) as cnt1
- from masacb.tb_cb_rs_echnl_key_serv_m t
- where chnl_type_id in
- ('1', '2', '3', '4', '5', '6', '100', '200', '300')
- group by t.CUST_LVL_ID, t.chnl_type_id
- order by t.cust_lvl_id)
- group by CUST_LVL_ID