DROP PROCEDURE IF EXISTS Pro_query_change_charge_by_layer_report;
CREATE PROCEDURE Pro_query_change_charge_by_layer_report (
IN cityCode varchar(32),/*城市code*/
IN areaCode varchar(16),/*区域code*/
IN energyStation INT,/*能源站code*/
IN beginDateTime datetime,/*开始时间*/
IN endDateTime datetime/*结束时间*/
)
BEGIN
SET @belongCity = cityCode;/*所属城市code*/
set @area=areaCode;/*区域*/
set @energyStation=energyStation; /*能源站code*/
set @beginDateTime=(
CASE
WHEN ISNULL(beginDateTime) or beginDateTime='' THEN
date_sub(curdate(), INTERVAL 6 DAY)
ELSE
DATE_FORMAT(beginDateTime,'%Y-%m-%d')
END
); /*起始时间*/
set @endDateTime=(
CASE
WHEN ISNULL(endDateTime) or endDateTime='' THEN
curdate()
ELSE
DATE_FORMAT(endDateTime,'%Y-%m-%d')
END
); /*结束时间*/
/*结束时间*/
select
v_worklist.station_name,/*站点名称*/
v_worklist.station_id,/*网点id*/
sum(
case when (v_worklist.create_time BETWEEN @beginDateTime and @endDateTime)
or
(v_worklist.create_time>@beginDateTime and (v_worklist.end_time<@endDateTime or v_worklist.end_time is NULL ))
then 1
else 0
end
) as shouldChangeOrder,/*应换工单*/
sum(
case when v_worklist.create_time BETWEEN @beginDateTime and @endDateTime
then 1
else 0
end
) as newAddOrder,/*新增工单*/
sum(
case when v_worklist.end_time BETWEEN @beginDateTime and @endDateTime
then 1
else 0
end
) as realChangeOrder/*实换工单*/
from v_queryworkorderlist v_worklist
WHERE
(CASE WHEN IFNULL(@belongCity,'0')='0' OR @belongCity='' THEN 1 ELSE
CAST(LOCATE(@belongCity,v_worklist.belong_city) AS SIGNED )
END)=1
AND
(CASE WHEN IFNULL(@area,'0')='0' or @area='' THEN 1 ELSE
CAST(LOCATE(@area,v_worklist.belong_area) AS SIGNED )
END)=1
AND
(CASE WHEN IFNULL(@energyStation,'0')='0' THEN 1 ELSE
CAST(LOCATE(@energyStation,v_worklist.repository_id) AS SIGNED )
END)=1
group by v_worklist.station_id;
END;
mySql——case when else ....demo
最新推荐文章于 2024-07-21 03:09:53 发布