sql

删除重复数据，保留最小数据

SELECT * FROM complete where dm in (SELECT dm FROM complete GROUP BY dm HAVING count(1)>1);
DELETE FROM complete WHERE id in (SELECT id from (SELECT id FROM complete where dm in (SELECT dm FROM complete GROUP BY dm HAVING count(1)>1) AND id NOT in (SELECT min(id) FROM complete GROUP BY dm HAVING count(1)>1)) a);
　　

更改表数据类型

ALTER TABLE `all_record`
ENGINE=MyISAM;

ALTER TABLE `all_record`
ENGINE=InnoDB;

 

存储过程更新数据拿下一条记录

BEGIN
DECLARE udm VARCHAR(64);
DECLARE in_data_time int(10);
DECLARE in_zdf DECIMAL(10,2);
DECLARE in_done int;
DECLARE done int;
DECLARE u_c CURSOR FOR SELECT dm FROM all_record GROUP BY dm ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN u_c;
c:LOOP
FETCH u_c INTO udm;
if done=1 THEN
LEAVE c;
ELSE
SET @old_data_time = 0;
BEGIN
DECLARE in_u_c CURSOR FOR SELECT data_time,zdf FROM all_record WHERE dm=udm ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET in_done=1;
OPEN in_u_c;
in_c:LOOP
FETCH in_u_c INTO in_data_time,in_zdf;
if in_done=1 THEN
LEAVE in_c;
ELSE
UPDATE all_record SET next_result=in_zdf WHERE next_result=-110 AND dm=udm AND data_time=@old_data_time;
SET @old_data_time=in_data_time;
end if;
END LOOP in_c;
CLOSE in_u_c;
SET in_done=0;
END;
end if;
END LOOP c;
CLOSE u_c;
END

 

存储过程拿上一条记录

BEGIN
DECLARE udm VARCHAR(64);
DECLARE in_data_time int(10);
DECLARE in_zxj DECIMAL(10,2);
DECLARE in_done int;
DECLARE done int;
DECLARE u_c CURSOR FOR SELECT dm FROM all_record GROUP BY dm ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN u_c;
c:LOOP
FETCH u_c INTO udm;
if done=1 THEN
LEAVE c;
ELSE
SET @old_zxj = 0;
BEGIN
DECLARE in_u_c CURSOR FOR SELECT data_time,zxj FROM all_record WHERE dm=udm ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET in_done=1;
OPEN in_u_c;
in_c:LOOP
FETCH in_u_c INTO in_data_time,in_zxj;
if in_done=1 THEN
LEAVE in_c;
ELSE
UPDATE all_record SET zs=@old_zxj WHERE zs=-11 AND dm=udm AND data_time=in_data_time;
SET @old_zxj=in_zxj;
end if;
END LOOP in_c;
CLOSE in_u_c;
SET in_done=0;
END;
end if;
END LOOP c;
CLOSE u_c;
END