1，select curdate() /*2016-10-08*/
2,select date_sub(curdate(), INTERVAL 6 DAY) /*2016-10-02*/
3,case when then else 拼接条件查询：
WHERE
(CASE WHEN IFNULL(@belongCity,'0')='0' OR @belongCity='' THEN 1 ELSE
CAST(LOCATE(@belongCity,worker_order.belong_city) AS SIGNED )
END)=1
AND
(CASE WHEN IFNULL(@energyStation,'0')='0' THEN 1 ELSE
CAST(LOCATE(@energyStation,worker_order.repository_id) AS SIGNED )
END)=1
AND
(CASE WHEN IFNULL(@serviceStatioin,'0')='0' THEN 1 ELSE
CAST(LOCATE(@serviceStatioin,worker_order.station_id) AS SIGNED )
END)=1
AND
DATE_FORMAT(worker_order.create_time, '%Y-%m-%d') BETWEEN DATE_FORMAT(cast(@beginDateTime as datetime), '%Y-%m-%d') and DATE_FORMAT(cast(@endDateTime as datetime), '%Y-%m-%d')
GROUP BY DATE_FORMAT(worker_order.create_time,'%Y-%m-%d');
或者是这种：
WHERE LOCATE((CASE WHEN IFNULL(@belongCity,'0')='0' OR @belongCity='' THEN worker_order.belong_city ELSE @belongCity END),worker_order.belong_city)>0 AND LOCATE((CASE WHEN IFNULL(@energyStation,'0')='0' THEN worker_order.repository_id ELSE @energyStation END),worker_order.repository_id)>0 AND LOCATE((CASE WHEN IFNULL(@serviceStatioin,'0')='0' THEN worker_order.station_id ELSE @serviceStatioin END),worker_order.station_id)>0 AND DATE_FORMAT(worker_order.create_time, '%Y-%m-%d') BETWEEN DATE_FORMAT(cast(@beginDateTime as datetime), '%Y-%m-%d') and DATE_FORMAT(cast(@endDateTime as datetime), '%Y-%m-%d') GROUP BY DATE_FORMAT(worker_order.create_time,'%Y-%m-%d');
4,拼接starttime和endtime:
set @beginDateTime=( CASE WHEN ISNULL(beginDateTime) THEN date_sub(curdate(), INTERVAL 6 DAY) ELSE DATE_FORMAT(beginDateTime,'%Y-%m-%d') END ); /*起始时间*/ set @endDateTime=( CASE WHEN ISNULL(endDateTime) THEN curdate() ELSE DATE_FORMAT(endDateTime,'%Y-%m-%d') END ); /*结束时间*/
5,创建存储过程及调用存储过程：
DROP PROCEDURE IF EXISTS Pro_query_work_order_report;
CREATE PROCEDURE Pro_query_work_order_report (
IN cityCode varchar(32),/*城市code*/
IN energyStation INT,/*能源站code*/
IN serviceStatioin INT,/*网点code*/
IN beginDateTime datetime,/*开始时间*/
IN endDateTime datetime/*结束时间*/
)
BEGIN
SET @belongCity = cityCode;/*所属城市code*/
set @energyStation=energyStation; /*能源站code*/
set @serviceStatioin=serviceStatioin; /*站点code*/
set @beginDateTime=(
CASE
WHEN ISNULL(beginDateTime) THEN
date_sub(curdate(), INTERVAL 6 DAY)
ELSE
DATE_FORMAT(beginDateTime,'%Y-%m-%d')
END
); /*起始时间*/
set @endDateTime=(
CASE
WHEN ISNULL(endDateTime) THEN
curdate()
ELSE
DATE_FORMAT(endDateTime,'%Y-%m-%d')
END
); /*结束时间*/
select
DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') as create_time,/*创建时间*/
( select count(0)
from biz_work_order tmp
where
DATE_FORMAT(tmp.create_time,'%Y-%m-%d')=DATE_FORMAT(worker_order.create_time,'%Y-%m-%d')
or (tmp.create_time<DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') and tmp.`status` in (1,2,4))
)as should_change_worker_order,/*应换工单*/
(
select count(0)
from biz_work_order tmp
where
DATE_FORMAT(tmp.create_time,'%Y-%m-%d')=DATE_FORMAT(worker_order.create_time,'%Y-%m-%d')
) as new_add_work_order,/*新增工单*/
(
select count(0)
from biz_work_order tmp
where DATE_FORMAT(tmp.create_time,'%Y-%m-%d')=DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') and tmp.`status`=3
)as today_finished_order,/*今天已完成工单*/
(
select count(0)
from biz_work_order tmp
where DATE_FORMAT(tmp.create_time,'%Y-%m-%d')=DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') and tmp.`status` in(1,2,4)
) as today_unfinished_order,/*今天未完成订单*/
cast(
(select count(0)
from biz_work_order tmp
where DATE_FORMAT(tmp.create_time,'%Y-%m-%d')=DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') and tmp.`status`=3
)
/
( select count(0)
from biz_work_order tmp
where
DATE_FORMAT(tmp.create_time,'%Y-%m-%d')=DATE_FORMAT(worker_order.create_time,'%Y-%m-%d')
or (tmp.create_time<DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') and tmp.`status` in (1,2,4))
)
as decimal(18, 2)
) as success_percent /*成功率*/
from biz_work_order worker_order
WHERE
(CASE WHEN IFNULL(@belongCity,'0')='0' OR @belongCity='' THEN 1 ELSE
CAST(LOCATE(@belongCity,worker_order.belong_city) AS SIGNED )
END)=1
AND
(CASE WHEN IFNULL(@energyStation,'0')='0' THEN 1 ELSE
CAST(LOCATE(@energyStation,worker_order.repository_id) AS SIGNED )
END)=1
AND
(CASE WHEN IFNULL(@serviceStatioin,'0')='0' THEN 1 ELSE
CAST(LOCATE(@serviceStatioin,worker_order.station_id) AS SIGNED )
END)=1
AND
DATE_FORMAT(worker_order.create_time, '%Y-%m-%d') BETWEEN DATE_FORMAT(cast(@beginDateTime as datetime), '%Y-%m-%d') and DATE_FORMAT(cast(@endDateTime as datetime), '%Y-%m-%d')
GROUP BY DATE_FORMAT(worker_order.create_time,'%Y-%m-%d');
END;
6,调用：（mysql 使用call进行调用，且不支持默认参数；sql server 使用exec进行调用，支持默认参数，有默认值的参数可以不传）
call Pro_query_work_order_report('50010',NULL,270,date_sub(curdate(), INTERVAL 6 DAY),curdate())
发现mysql怎么数据量大了，还没有sql server好用。。。难道是我的幻觉么。。
