|
原sql
SELECT coalesce(SUM(telecom_count), 0) AS telecom_count,
coalesce(SUM(telecom_money), 0) AS telecom_money,
coalesce(SUM(mobile_count), 0) AS mobile_count,
coalesce(SUM(mobile_money), 0) AS mobile_money,
coalesce(SUM(unicom_count), 0) AS unicom_count,
coalesce(SUM(unicom_money), 0) AS unicom_money,
coalesce(SUM(fixandphs_count), 0) AS fixandphs_count,
coalesce(SUM(fixandphs_money), 0) AS fixandphs_money,
coalesce(SUM(recharge_num), 0) AS recharge_num,
coalesce(SUM(recharge_money), 0) AS recharge_money
FROM (SELECT a.agent_id,
coalesce(SUM(a.telecom_count), 0) AS telecom_count,
coalesce(SUM(a.telecom_money), 0) AS telecom_money,
coalesce(SUM(a.mobile_count), 0) AS mobile_count,
coalesce(SUM(a.mobile_money), 0) AS mobile_money,
coalesce(SUM(a.unicom_count), 0) AS unicom_count,
coalesce(SUM(a.unicom_money), 0) AS unicom_money,
coalesce(SUM(a.fixandphs_count), 0) AS fixandphs_count,
coalesce(SUM(a.fixandphs_money), 0) AS fixandphs_money,
coalesce(SUM(a.recharge_num), 0) AS recharge_num,
coalesce(SUM(a.recharge_money), 0) AS recharge_money
FROM tb_agent_charge_count a
LEFT JOIN tb_agent b
ON a.agent_id = b.id
WHERE a.agent_id != -10010
AND a.create_date >=
to_date('2014-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND a.create_date <
to_date('2014-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
GROUP BY a.agent_id)
改后
SELECT coalesce(SUM(telecom_count), 0) AS telecom_count,
coalesce(SUM(telecom_money), 0) AS telecom_money,
coalesce(SUM(mobile_count), 0) AS mobile_count,
coalesce(SUM(mobile_money), 0) AS mobile_money,
coalesce(SUM(unicom_count), 0) AS unicom_count,
coalesce(SUM(unicom_money), 0) AS unicom_money,
coalesce(SUM(fixandphs_count), 0) AS fixandphs_count,
coalesce(SUM(fixandphs_money), 0) AS fixandphs_money,
coalesce(SUM(recharge_num), 0) AS recharge_num,
coalesce(SUM(recharge_money), 0) AS recharge_money
FROM tb_agent_charge_count a
WHERE a.agent_id != -10010
AND a.create_date >=
to_date('2014-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND a.create_date <
to_date('2014-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
|
|