本文共 3268 字，大约阅读时间需要 10 分钟。
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)>0AND LOCATE((CASE WHEN IFNULL(@energyStation,'0')='0' THEN worker_order.repository_id ELSE @energyStation END),worker_order.repository_id)>0AND LOCATE((CASE WHEN IFNULL(@serviceStatioin,'0')='0' THEN worker_order.station_id ELSE @serviceStatioin END),worker_order.station_id)>0AND 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/*结束时间*/)BEGINSET @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
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好用。。。难道是我的幻觉么。。
转载地址：http://ypbql.baihongyu.com/