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<@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;