在Mysql利用SQL根据停车时间计算停车费，计算实际应收款

 

 1 =========添加TrueMoney字段存储实际付费金额===============
 2 
 3 ALTER TABLE `db_newg3_main_0524`.`box_out_record_2020` ADD COLUMN `TrueMoney` DECIMAL(7,2) NULL DEFAULT 0 AFTER `ParkNo`
 4 
 5 ALTER TABLE `db_newg3_main_0524`.`box_out_record_2021` ADD COLUMN `TrueMoney` DECIMAL(7,2) NULL DEFAULT 0 AFTER `ParkNo`
 6 
 7 ALTER TABLE `db_newg3_main_0524`.`box_out_record_2022` ADD COLUMN `TrueMoney` DECIMAL(7,2) NULL DEFAULT 0 AFTER `ParkNo`
 8 
 9 ALTER TABLE `db_newg3_main_0524`.`box_out_record_2023` ADD COLUMN `TrueMoney` DECIMAL(7,2) NULL DEFAULT 0 AFTER `ParkNo`
10 
11 ALTER TABLE `db_newg3_main_0524`.`box_out_record_2024` ADD COLUMN `TrueMoney` DECIMAL(7,2) NULL DEFAULT 0 AFTER `ParkNo`
12 
13 ALTER TABLE `db_newg3_main_0524`.`box_out_record_2025` ADD COLUMN `TrueMoney` DECIMAL(7,2) NULL DEFAULT 0 AFTER `ParkNo`
14 
15 添加计算停车费的mysql函数。
16 ===================
17 CREATE DEFINER=`root`@`localhost` FUNCTION `calculate_parking_fee`(entry_time DATETIME, exit_time DATETIME) RETURNS decimal(10,2)
18 DETERMINISTIC
19 BEGIN
20 DECLARE total_hours DECIMAL(10, 2);
21 DECLARE total_fee DECIMAL(10, 2);
22 DECLARE full_days INT;
23 DECLARE remaining_hours DECIMAL(10, 2);
24 DECLARE remaining_fee DECIMAL(10, 2);
25 
26 -- 计算总停车小时数
27 SET total_hours = TIMESTAMPDIFF(MINUTE, entry_time, exit_time) / 60.0;
28 
29 -- 如果停车时间不足半小时，免费
30 IF total_hours <= 0.5 THEN
31 SET total_fee = 0.0;
32 ELSE
33 -- 计算完整的24小时天数
34 SET full_days = FLOOR(total_hours / 24);
35 
36 -- 计算剩余不足24小时的部分
37 SET remaining_hours = MOD(total_hours, 24);
38 
39 -- 计算剩余部分的费用
40 IF remaining_hours <= 0.5 THEN
41 SET remaining_fee = 0.0;
42 ELSEIF remaining_hours * 5 > 30 THEN
43 SET remaining_fee = 30.0;
44 ELSE
45 SET remaining_fee = CEIL(remaining_hours) * 5;
46 END IF;
47 
48 -- 总费用 = 完整天数费用 + 剩余部分费用
49 SET total_fee = full_days * 30 + remaining_fee;
50 END IF;
51 
52 RETURN total_fee;
53 END
54 
55 
56 =======计算实际停车费，存入TrueMoney属性=========
57 update db_newg3_main_0524.box_out_record_2020 set TrueMoney=calculate_parking_fee(InTime,OutTime) where InTime>'2005-02-18 16:12:52';
58 update db_newg3_main_0524.box_out_record_2021 set TrueMoney=calculate_parking_fee(InTime,OutTime) where InTime>'2005-02-18 16:12:52';
59 update db_newg3_main_0524.box_out_record_2022 set TrueMoney=calculate_parking_fee(InTime,OutTime) where InTime>'2005-02-18 16:12:52';
60 update db_newg3_main_0524.box_out_record_2023 set TrueMoney=calculate_parking_fee(InTime,OutTime) where InTime>'2005-02-18 16:12:52';
61 update db_newg3_main_0524.box_out_record_2024 set TrueMoney=calculate_parking_fee(InTime,OutTime) where InTime>'2005-02-18 16:12:52';
62 update db_newg3_main_0524.box_out_record_2025 set TrueMoney=calculate_parking_fee(InTime,OutTime) where InTime>'2005-02-18 16:12:52';
63 
64  
65 
66  
67 
68  
69 
70 
71 =====导出数据SQL======
72 
73 SELECT EnterRecordID as '入场记录ID',PersonName as '车主名字',SetmealName as '套餐名称',CredentialNO as '车牌号',InTime as '入场时间',OutTime as '出场时间',AccountReceivable as '应收金额',Charging as '计费',TrueMoney as '实际应付' FROM db_newg3_main_0524.box_out_record_2020 where TrueMoney>0 and TrueMoney!=Charging;
74 
75 SELECT EnterRecordID as '入场记录ID',PersonName as '车主名字',SetmealName as '套餐名称',CredentialNO as '车牌号',InTime as '入场时间',OutTime as '出场时间',AccountReceivable as '应收金额',Charging as '计费',TrueMoney as '实际应付' FROM db_newg3_main_0524.box_out_record_2021 where TrueMoney>0 and TrueMoney!=Charging;
76 
77 SELECT EnterRecordID as '入场记录ID',PersonName as '车主名字',SetmealName as '套餐名称',CredentialNO as '车牌号',InTime as '入场时间',OutTime as '出场时间',AccountReceivable as '应收金额',Charging as '计费',TrueMoney as '实际应付' FROM db_newg3_main_0524.box_out_record_2022 where TrueMoney>0 and TrueMoney!=Charging;
78 SELECT EnterRecordID as '入场记录ID',PersonName as '车主名字',SetmealName as '套餐名称',CredentialNO as '车牌号',InTime as '入场时间',OutTime as '出场时间',AccountReceivable as '应收金额',Charging as '计费',TrueMoney as '实际应付' FROM db_newg3_main_0524.box_out_record_2023 where TrueMoney>0 and TrueMoney!=Charging;
79 SELECT EnterRecordID as '入场记录ID',PersonName as '车主名字',SetmealName as '套餐名称',CredentialNO as '车牌号',InTime as '入场时间',OutTime as '出场时间',AccountReceivable as '应收金额',Charging as '计费',TrueMoney as '实际应付' FROM db_newg3_main_0524.box_out_record_2024 where TrueMoney>0 and TrueMoney!=Charging;
80 SELECT EnterRecordID as '入场记录ID',PersonName as '车主名字',SetmealName as '套餐名称',CredentialNO as '车牌号',InTime as '入场时间',OutTime as '出场时间',AccountReceivable as '应收金额',Charging as '计费',TrueMoney as '实际应付' FROM db_newg3_main_0524.box_out_record_2025 where TrueMoney>0 and TrueMoney!=Charging;
81 
82 =========获取2020进出明细数据========
83 
84 SELECT EnterRecordID as '入场记录ID',PersonName as '车主名字',SetmealName as '套餐名称',CredentialNO as '车牌号',InTime as '入场时间',OutTime as '出场时间',AccountReceivable as '应收金额',Charging as '计费',TrueMoney as '实际应付' FROM db_newg3_main_0524.box_out_record_2020;
85 
86 =========获取2025统计数据==========
87 
88 SELECT count(EnterRecordID) as "月租车进出数量" FROM db_newg3_main_0524.box_out_record_2025 where SetmealName like '%月%';
89 
90 SELECT count(EnterRecordID) as "总进出数量" FROM db_newg3_main_0524.box_out_record_2025;
91 
92 SELECT sum(AccountReceivable) as "AccountReceivables",sum(TrueMoney) as "TrueMoneys" FROM db_newg3_main_0524.box_out_record_2025 where SetmealName not like '%月%';
93 
94  
95 
96  
97 
98

 

posted @ 2025-06-15 19:26  nanahome  阅读(47)  评论(0)    收藏  举报