MySQL 存储过程常用SQL语句收集

来源：互联网发布：java支付平台开发demo 编辑：程序博客网时间：2024/05/16 08:49

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 ANDDATE_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');

或者是这种：

WHERELOCATE((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)>0ANDDATE_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=(CASEWHEN ISNULL(beginDateTime) THENdate_sub(curdate(), INTERVAL 6 DAY)ELSEDATE_FORMAT(beginDateTime,'%Y-%m-%d')END); /*起始时间*/set @endDateTime=(CASEWHEN ISNULL(endDateTime) THENcurdate()ELSEDATE_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=(CASEWHEN ISNULL(beginDateTime) THENdate_sub(curdate(), INTERVAL 6 DAY)ELSEDATE_FORMAT(beginDateTime,'%Y-%m-%d')END); /*起始时间*/set @endDateTime=(CASEWHEN ISNULL(endDateTime) THENcurdate()ELSEDATE_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_orderWHERE(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 ANDDATE_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好用。。。难道是我的幻觉么。。

0 0