mysql存储food_Mysql存储过程

一：带变量

delimiter$$

CREATE  PROCEDURE `echarts`()

begin

DECLARE member_count int DEFAULT 0;

SELECT COUNT(id) INTO member_count FROM common_party_member;

delete from common_echarts;

Insert into common_echarts

select case when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 18 AND 29 then '18-29岁'

when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 30 AND 39 then '30-39岁'

when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 40 AND 49 then '40-49岁'

when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 50 AND 59 then '50-59岁'

when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 60 AND 69 then '60-69岁'

when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 70 AND 79 then '70-79岁'

when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) > 80 then '80岁以上' end as text,count(id) count,member_count total,ROUND(count(id)/member_count*100,1) percent,'age' type from common_party_member GROUP BY text

union

select education text,count(id) count,member_count total,ROUND(count(id)/member_count*100,1) percent,'education' type

from common_party_member GROUP BY text

end

delimiter;

二：带输入参数

delimiter$$

CREATE  PROCEDURE `group_cost_statistics`(in begin_date datetime,in end_date datetime,in groupid varchar(36))

begin

select max(person_num) person_num,max(fate) fate,max(budget) budget,max(foodcost) foodcost,max(livingcost)livingcost,max( clothingcost)clothingcost ,a.group_id,m.case_name group_name,max(total) total

from(

select group_id,count(id)person_num, max(CASE

WHEN leave_date is null THEN

datediff(now(),checkin_date)

ELSE

datediff(leave_date,checkin_date)

END) fate,sum(CASE

WHEN leave_date is null THEN

datediff(now(),checkin_date)

ELSE

datediff(leave_date,checkin_date)

END*140) budget,0 foodcost,0 livingcost,0 clothingcost,0 total from rm_checkin

where datediff(now(),checkin_date)>0 and (begin_date is null or checkin_date >= begin_date) and (end_date is null or checkin_date <= end_date)

GROUP BY group_id

UNION

select group_id,0 person_num,0 fate,0 budget,

sum(case when consumption_type='食品申请' then consumption_price end)foodcost,

sum(case when consumption_type='物品申请' then consumption_price end)livingcost ,

sum(case when consumption_type='物品领用' then consumption_price end)clothingcost,

sum(consumption_price) total

from sp_group_bill where audit_state=1 and (begin_date is null or consumption_date >= begin_date) and (end_date is null or consumption_date <= end_date) GROUP BY group_id)a

inner join sp_group_main m on a.group_id=m.id

where (m.leave_date is null or m.leave_date='') and (groupid is null or m.id = groupid)

GROUP BY a.group_id ;

end

delimiter;