notes/work/移动杭研/问题处理/2024-01/0120-统计商用客户对应的平面信息.md

来源：鹏豪

获得商用客户

select DISTINCT(bi.id),  
               bi.order_num           as '开通单号',  
               ei.enterprise_name     as '企业名称',  
               ei.enterprise_cp_id    as '企业CP_ID',  
               pi.business_type       as '产品类型',  
               pi.product_id          as '订购ID',  
               pi.business_start_time as '开通时间',  
               ei.enterprise_id       as 'tId'  
#                 ,  
#                ssdc.domain,  
#                ssdc.domain_plain  
from bbossorder_info bi  
         left join productid_info pi on bi.product_id = pi.product_id  
         left join enterprise_info ei on pi.ec_id = ei.ec_id  
         left join ep_product_chargemode epc on ei.enterprise_id = epc.enterprise_id  
#          LEFT JOIN self_service_domain_config ssdc ON ei.enterprise_id = ssdc.tenant_id  
  
where pi.cancel_status = 1  
  and ei.source = 'BSS'  
  and epc.product_state = 4  
order by pi.business_start_time;

获得公司对应的平面信息

SELECT tenant_id, GROUP_CONCAT(domain) AS domains  
FROM self_service_domain_config  
WHERE tenant_id IN ('yloiy4ms-x7sldjk9gdnt2cib', 'migbq85b-3njr7ylfi1jantml', 'liyk5qv3-3frd1daqmgu0sfld',  
                    '2gc5ewux-bpel3akyqelz96lm', 'xhrsfafr-d23mmlopgfouekx2', 'p7xvifuo-2bx1te6ywrw294ua')  
GROUP BY tenant_id;