出入库数据为0的问题解决 20251222 —— select类型的语句 #178

New Issue

2025-12-22T17:34:32+08:00

bigtran commented

2025-12-22 17:34:32 +08:00

时间大于1秒的select 语句大概有五种类型

前面几种通过复合索引，基本上，时间可以控制到1秒和0.1秒以内

select count(id) as count,round(sum(weight),2) as weight,waste_type from opm_mw_info_data where organ_name='新源县妇幼保健院' and waste_status != '误操作' and in_ware_time >= '2025-12-15 12:40:11'  and in_ware_time <= '2025-12-17 12:40:15' and waste_status !='收集' and waste_status !='误操作' and waste_status != '出库' group by waste_type;


CREATE INDEX idx_mw_query1
      ON opm_mw_info_data
         (organ_name(20),
          hand_time,
          dept_name(10),
          waste_status(10),
          waste_type(10));


select count(id) as count,round(sum(weight),2) as weight,waste_type from opm_mw_info_data where organ_name='新源县妇幼保健院' and waste_status != '误操作' and in_ware_time >= '2025-12-17 12:40:15'  and in_ware_time <= '2025-12-19 12:40:16' and waste_status !='收集' and waste_status !='误操作' and waste_status != '出库' group by waste_type;


CREATE INDEX idx_mw_query2
      ON opm_mw_info_data
         (organ_name(20),
          in_ware_time,
          dept_name(10),
          waste_status(10),
          waste_type(10));
					
select created_at from opm_mw_raw_data where organ_id='330' and device_id='2575' and `code` = 'InOutWare' and type ='in1' and id <= (SELECT MAX(id) FROM opm_mw_raw_data where organ_id='330')  order by id desc limit 0,15;

					
					
CREATE INDEX idx_raw_query3  
			 ON opm_mw_raw_data  
			    (organ_id, 
					device_id, 
					code, 
					type, 
					id DESC);


select count(id) as count,round(sum(weight),2) as weight,waste_type from opm_mw_info_data where organ_name='成都妇女儿童中心医院' and scale ='s00000abuf' and waste_status != '误操作' and waste_status != '出库' and waste_status != '入库' and recl_time >= '2025-11-07 14:29:48'  and recl_time <= '2025-12-17 17:16:28' group by waste_type;

CREATE INDEX idx_mw_query4
      ON opm_mw_info_data
         (organ_name(20),
				 scale(20),
          recl_time,
          waste_status(10),
          waste_type(10));
					

select created_at from opm_mw_raw_data where `code`='InOutWare' and type ='ot1' and url like 'c=api&a=InOutWare&scode=113140%' and url not like '%r180062509%' and url not like '%r180062688%' and url not like '%r180062880%' and url not like '%r180062295%' order by id desc limit 0,15;

20251222~20251222 之间的数据分析

## 时间大于1秒的select 语句大概有五种类型 ### 前面几种通过复合索引，基本上，时间可以控制到1秒和0.1秒以内 ``` select count(id) as count,round(sum(weight),2) as weight,waste_type from opm_mw_info_data where organ_name='新源县妇幼保健院' and waste_status != '误操作' and in_ware_time >= '2025-12-15 12:40:11' and in_ware_time <= '2025-12-17 12:40:15' and waste_status !='收集' and waste_status !='误操作' and waste_status != '出库' group by waste_type; CREATE INDEX idx_mw_query1 ON opm_mw_info_data (organ_name(20), hand_time, dept_name(10), waste_status(10), waste_type(10)); select count(id) as count,round(sum(weight),2) as weight,waste_type from opm_mw_info_data where organ_name='新源县妇幼保健院' and waste_status != '误操作' and in_ware_time >= '2025-12-17 12:40:15' and in_ware_time <= '2025-12-19 12:40:16' and waste_status !='收集' and waste_status !='误操作' and waste_status != '出库' group by waste_type; CREATE INDEX idx_mw_query2 ON opm_mw_info_data (organ_name(20), in_ware_time, dept_name(10), waste_status(10), waste_type(10)); select created_at from opm_mw_raw_data where organ_id='330' and device_id='2575' and `code` = 'InOutWare' and type ='in1' and id <= (SELECT MAX(id) FROM opm_mw_raw_data where organ_id='330') order by id desc limit 0,15; CREATE INDEX idx_raw_query3 ON opm_mw_raw_data (organ_id, device_id, code, type, id DESC); select count(id) as count,round(sum(weight),2) as weight,waste_type from opm_mw_info_data where organ_name='成都妇女儿童中心医院' and scale ='s00000abuf' and waste_status != '误操作' and waste_status != '出库' and waste_status != '入库' and recl_time >= '2025-11-07 14:29:48' and recl_time <= '2025-12-17 17:16:28' group by waste_type; CREATE INDEX idx_mw_query4 ON opm_mw_info_data (organ_name(20), scale(20), recl_time, waste_status(10), waste_type(10)); select created_at from opm_mw_raw_data where `code`='InOutWare' and type ='ot1' and url like 'c=api&a=InOutWare&scode=113140%' and url not like '%r180062509%' and url not like '%r180062688%' and url not like '%r180062880%' and url not like '%r180062295%' order by id desc limit 0,15; ``` ## 20251222~20251222 之间的数据分析 ![image.png](/attachments/c0b7ce63-00fc-4f9c-9a62-dac1ea31849c) ![image.png](/attachments/9481da99-dd7e-48d9-8975-be26039d2d25) ![image.png](/attachments/1651dd29-3cea-4f8d-9f6d-5bad037b035a)

image.png

69 KiB

image.png

83 KiB

image.png

55 KiB

bigtran commented

2025-12-22 19:17:06 +08:00