BEGIN
	DECLARE In_StartTime  VARCHAR(64);
	DECLARE In_StopTime   VARCHAR(64);
	DECLARE IN_User_id    VARCHAR(64);
	DECLARE IN_work_no    VARCHAR(20);
	DECLARE IN_Office_id  VARCHAR(64);
	DECLARE IN_Cus_source VARCHAR(100);
 
 
if in_begindate is NULL or in_begindate ='' THEN
	#set In_StartTime = timestamp(date_add(curdate(), interval - day(curdate()) + 1 day));##默認查詢開始時間為當月1日
set In_StartTime = DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00');##默認查詢開始時間為昨天開始
ELSE
	SET In_StartTime = in_begindate;
END IF;
 
if in_enddate is NULL or in_enddate ='' THEN
	set In_StopTime = DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59');##默認查詢結(jié)束時間為昨天時間
ELSE
	SET In_StopTime = in_enddate;
END IF;
 
 
 
SET IN_User_id = in_userid;
SET IN_work_no =in_user_work_no;
SET IN_Office_id = in_offid;
SET IN_Cus_source = in_custsouce;
 
 
	select DTA.*,
(case when DTB.callcount is  NULL then 0 ELSE DTB.callcount  END) as all_call_num,	##總呼出量
(case when DTB.calllens is  NULL then 0 ELSE
	concat(concat(DTB.calllens div 3600,'時'),concat(DTB.calllens mod 3600 div 60,'分'),concat(DTB.calllens mod 3600 mod 60 mod 60,'秒'))
 END) as all_call_time,		##總呼出時長
 
(case when DTB.effcount is  NULL then 0 ELSE DTB.effcount END) as eff_call_num,			##有效呼出量
(case when DTB.effcountlens is  NULL then 0 ELSE 
	concat(concat(DTB.effcountlens div 3600,'時'),concat(DTB.effcountlens mod 3600 div 60,'分'),concat(DTB.effcountlens mod 3600 mod 60 mod 60,'秒'))
END) as eff_call_time,## 有效呼出時長
 
(case when DTC.exchange_num is  NULL then 0 ELSE DTC.exchange_num END) as exchange_num,## 交流次數(shù)
(case when DTD.summary_num is  NULL then 0 ELSE DTD.summary_num END) as summary_num, ## 總結(jié)次數(shù) 
(case when DTE.WorkCount is  NULL then 0 ELSE DTE.WorkCount END) as worksheet_num, ## 總機會點數(shù)
(case when DTE.WorkQDCount is  NULL then 0 ELSE DTE.WorkQDCount END) as sign_worksheet_num,##簽單機會點數(shù)
 
 
(CASE WHEN DTE.WorkQDCount = 0 	then 0
			WHEN DTE.WorkCount   = 0 	then 0
			when   DTE.WorkCount  is NULL  then 0
			when  DTE.WorkQDCount is NULL  then 0
 
 ELSE  concat((ROUND( (IFNULL(DTE.WorkQDCount,0)/DTE.WorkCount),2)  *100 ),'%')
 END ) as WorkRate, ## 成功率
 
(case when DTE.WorkSum  is  NULL then 0 ELSE DTE.WorkSum  END) as sales_volume ##成交總金額
 
FROM
(
 
 
## 公司相關(guān)信息和人員賬號
SELECT TTA.office_id,TTA.company,user_id,work_no,user_name from 
(SELECT id as office_id, name as company FROM sys_office WHERE del_flag=0) TTA
LEFT OUTER JOIN 
(SELECT id as user_id ,work_no, name as user_name,office_id from sys_user where del_flag=0 and   
	(is_disabled ='1'  or is_disabled='' or ISNULL(is_disabled) )
) TTB
ON ( TTA.office_id =TTB.office_id) WHERE 
		( TTB.user_id=IN_User_id or  IN_User_id is null  or IN_User_id = '') 
and ( TTB.work_no=IN_work_no or  IN_work_no is null  or IN_work_no = '') 
 
and  ( TTB.office_id=IN_Office_id or  IN_Office_id is null  or IN_Office_id = '')
) DTA 
LEFT OUTER JOIN 
 
 
(##通話相關(guān)次數(shù)及時長，有效通話次數(shù)及時長
SELECT TA.agent_id ,
SUM(callcount) as callcount,
SUM(calllens) as calllens,
SUM(effcount) as effcount,
SUM(effcountlens) as effcountlens
 from 
(select agent_id,
		 (case when (agent_id is NOT NULL or times  is NOT NULL)  then 1 ELSE 0 END) as callcount,
		(case when (agent_id is NOT NULL  or times  is NOT NULL )then times else 0 end) as calllens,
		(case when  times >=30 then 1 else 0 end) as effcount,
		(case when  times >=30 then times else 0 end) as effcountlens
 
 
 from  crm_hw_call where call_begintime >=In_StartTime  and STR_TO_DATE(call_begintime,'%Y-%m-%d %H:%i:%s')<=In_StopTime
 
) TA GROUP BY TA.agent_id
 
 
 
 
) DTB on (DTA.work_no=DTB.agent_id)
LEFT OUTER JOIN 
(## 交流次數(shù)
SELECT TC.create_by,IFNULL(SUM(exchange_num),0) as exchange_num
FROM
(
 
SELECT wk.create_by, wk.user_type,
 (	CASE WHEN ex.create_by is not NULL then 1 else 0 end) as  exchange_num 
 from crm_worksheet wk  LEFT OUTER JOIN  crm_wkst_exchange_record ex on wk.worksheet_no=ex.worksheet_no
	where  ex.create_by is not NULL and ex.del_flag=0 and wk.del_flag=0
	and ( wk.user_type ='sys_basic_hua_wei' or  'sys_basic_hua_wei' is null  or 'sys_basic_hua_wei' = '')
	and ex.create_date >=In_StartTime  and ex.create_date<=In_StopTime
 ) TC
			GROUP BY TC.create_by
		) DTC on (DTA.user_id = DTC.create_by)
LEFT OUTER JOIN
 
 
(## 總結(jié)次數(shù)
SELECT TD.create_by,SUM(TD.summary_num) as summary_num
FROM
(SELECT create_by, 
(CASE WHEN create_by is not NULL then 1 else 0 end) as summary_num 
FROM crm_day_report  where del_flag=0 
	and create_date >=In_StartTime  and create_date<=In_StopTime
) TD GROUP BY TD.create_by
) DTD on (DTA.user_id = DTD.create_by)
LEFT OUTER JOIN
 
 
 
## 簽單次數(shù)，工單總數(shù)，簽單總額
(SELECT 
	TE.create_by,
	SUM(WorkCount) AS WorkCount,
	SUM(WorkQDCount) AS WorkQDCount,
	SUM(WorkSum) AS WorkSum
FROM
(SELECT create_by,
(CASE WHEN create_by is not null THEN 1 else 0 end ) as WorkCount,
(CASE WHEN create_by is not null and important_degree='sys_basic_qian_shu_he_tong' THEN 1 else 0 end ) as WorkQDCount,
(CASE WHEN create_by is not null and important_degree='sys_basic_qian_shu_he_tong' THEN IFNULL(solution,0) else 0 end ) as WorkSum
 from crm_worksheet where del_flag=0  
	and ( user_type =IN_Cus_source or  IN_Cus_source is null  or IN_Cus_source = '')
	and create_date >=In_StartTime  and create_date<=In_StopTime
 
) TE  GROUP BY TE.create_by
) DTE ON (DTA.user_id = DTE.create_by);
  SELECT  IN_work_no,in_user_work_no;
 
END
##輸入?yún)?shù)
in_begindate varchar(64),in_enddate varchar(64),in_userid varchar(64),in_offid varchar(64), in_custsouce  varchar(100),in_user_work_no varchar(20)
 

create procedure p1(存儲名)（in suppiler_num varchar(10)）
begin 
   		declare results int default 0;  #聲明變量并初始化
   		select count(*) into results    #賦值
   		from factory.suppiler
    	where factory.suppiler.suppiler_num=suppiler_num;
   	    select if (results>0,'成功','失敗' );  #使用
end
（參數(shù)列表: 模式  參數(shù)名  參數(shù)類型）
     模式 in(默認)、out、 inout：in 模式；out 模式；inout 模式

drop procedure p1 （并且只能一次刪除一個存儲過程）

call p1('s1')

  show create procedure p1

create function 函數(shù)名(參數(shù)列表) returns 返回類型
begin
   	函數(shù)體
   	
end
1、參數(shù)列表 （參數(shù)名 參數(shù)類型）
2、 函數(shù)體肯定有return 語句

select 函數(shù)名（參數(shù)列表）

-- 用戶表：
Create table  tUser  
(      /*用戶賬號*/ tuser_num varchar(10), 
     /* 用戶名稱 */tuser_name  varchar(50),
      /*登陸密碼 */ tuser_password  varchar(20),
     /* 聯(lián)系電話 */ tuser_tel  varchar(20),
     /* 郵箱地址 */tuser_maill  varchar(100),
      /*已購商品總額 */ tuser_sum_expence  numeric(10,2),
      /*送貨地址 */ tuser_addr   varchar(100)
)
--用戶登錄歷史記錄表：
Create table tUserHisRec (
        /*登錄序號*/ tuserhisrec_row int auto_increment,
        /*用戶賬號*/tuserhisrec_num varchar(10),
        /*登錄時間*/tuserhisrec_time datetime,
         primary key (tuserhisrec_row) #自增必須要設(shè)鍵
)
--商品列表：
Create table productsList(
        /*商品編號*/ productsList_num varchar(20),
        /*商品名稱*/ productsList_name  varchar(100),
        /*商品類別 */productsList_kind varchar(100),
        /*商品價格 */productsList_price numeric(10,2),
        /*出廠日期 */productsList_date datetime, 
        /*生產(chǎn)商 */productsList_suppiler varchar(200)
)
--用戶購買商品記錄：
Create table tUserOrder(
   	/*登陸序號*/ tuserorder_row int auto_increment,
   	/*用戶賬號 */tuser_num varchar(10),
   	/*商品編號 */productsList_num varchar(20),
   	/*購買時間 */tuserorder_time datetime,
   	/*送貨地址 */tuser_addr varchar(100),
   	primary key (tuserorder_row)
)

create procedure t2(in products_name varchar(100),in products_kind varchar(100))
begin
   select *,(select count(distinct tuser_num) from tUserOrder
    where `productsList_num`=a.productsList_num) as '已購用戶數(shù)量',
   (select tuser_num  from factory.tuserorder 
   where tuserorder_time=(select max(tuserorder_time) 
   from factory.tuserorder 
   where `productsList_num`=a.productsList_num
    group by a.productsList_num)) as '最近一次購買的用戶',
   (select max(tuserorder_time) from factory.tuserorder 
   where `productsList_num`=a.productsList_num
    group by a.productsList_num)as '最近一次購買的時間' 
   from factory.productslist  a where `productsList_num` in 
   (select productsList_num  from factory.productslist 
   where `productsList_name` like concat('%',products_name,'%')
   and `productsList_kind` like concat('%',products_kind,'%'));
end

		select staff_num from staff a where staff_salary<(
		select avg(staff_salary from staff 
		where staff_depart=a.staff_deapart)

create procedure t1(in use_num varcharacter(10),in product_num varcharacter(20))
begin
   declare tuser_addr1 varchar(100) ; 
   declare productsList_price1 numeric(10,2);
   select  tuser_addr into tuser_addr1
   from tUser 
   where tUser.tuser_num=use_num;
   select productsList_price into productsList_price1
   from factory.productsList
   where productsList_num=product_num;
   update tUser
  set tuser_sum_expence=tuser_sum_expence+productsList_price1 
   where tuser_num=use_num;
   insert into factory.tuserOrde(tuser_num,productsList_num,tuserorder_time,tuser_addr)
  values(use_num,product_num,now(),tuser_addr1);
end

create function t3(user_num varchar(10)) returns varchar(20) 
begin
   declare products_num varchar(20);
   select `productsList_num` into products_num from  
   (select count(`productsList_num`)as c1,`productsList_num` 
    from factory.tuserorder   where tuser_num=user_num
    group by `productsList_num`)as t1  having max(c1);
   return products_num;
end

select a from (select a,b,c from table1 where…)as t1