mysql case when else_mySql——case when else ....demo

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

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;