更新财务报表2

-- 查询月服务天数不等于 数据库中存的月服务天数

SELECT  b.day1,a.days_of_month  from erp_work_day_opt_info_copy a, (select count(DISTINCT service_day) as day1 , order_id from erp_work_day_opt_info_copy where service_day >= '2015-08-01' and  service_day <= '2015-08-01' and order_state =4  GROUP BY order_id )b where a.order_id = b.order_id  and   service_day = '2015-08-01' and order_state =4 and b.day1 != a.days_of_month

-- 查询更改前 和更改后的 月服务天数数据 select a.order_id,a.days_of_month,ac.days_of_month from erp_work_day_opt_info_copy ac,erp_work_day_opt_info a where ac.id = a.id and ac.order_state = 4 and a.days_of_month != ac.days_of_month and  ac.service_day = '2015-08-06'

-- 更新 月服务天数 UPDATE  erp_work_day_opt_info_copy a set  a.days_of_month =( SELECT day1 from (  select count(DISTINCT service_day) as day1,order_id  from erp_work_day_opt_info_copy where service_day >= '2015-08-01' and  service_day <= '2015-08-31' and order_state =4 GROUP BY order_id  ) b where a.order_id =b.order_id   ) where      service_day = '2015-08-31' and order_state =4 

-- 3号投放的,月服务天数 不等于1的错误数据 SELECT days_of_month  from erp_work_day_opt_info where order_id in(1116111412293638431, 111611201503260001, 161410111705426422, 16150523100003515937, 16150624114818157101, 1620150406140658405541, 1620150422183741941759)and  service_day = '2015-08-03' and order_state =4 ;

-- 查询8月1号 日客单价不等于 月服务金额 SELECT * FROM  erp_work_day_opt_info_copy a,  erp_work_order b WHERE  service_day like '%2015-08-01%' AND a.order_id = b.order_id and a.customer_price_per_day != a.money_of_month and a.order_state = 4

 

-- 查询

SELECT a .order_id from  erp_work_order a ,(SELECT  order_id,  count(DISTINCT service_day) FROM  erp_work_day_opt_info_copy WHERE  service_day LIKE '2015-08%' AND rest_service_days <= 0 AND order_state = 4 GROUP BY  order_id HAVING  count(DISTINCT service_day) > 1) b where a.order_id =b.order_id and a.inner_flag =0

 

-- 查询 剩余天数小于等于0的并且大于1天的 SELECT * from  erp_work_day_opt_info_copy where order_id in (SELECT  order_id   FROM  erp_work_day_opt_info_copy WHERE  service_day LIKE '2015-08%' AND rest_service_days <= 0 AND order_state = 4 GROUP BY  order_id HAVING  count(DISTINCT service_day) > 1 )

and service_day LIKE '2015-08%' AND rest_service_days <= 0  ORDER BY order_id desc