zycchen 发表于 2018-10-23 12:17:19

脑残的sql

原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 (SELECTa.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')


页: [1]
查看完整版本: 脑残的sql