首先建立表
CREATE TABLE `jy_data_sf6` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`par_id` int(11) DEFAULT NULL,
`unit_id` int(11) DEFAULT NULL ,
`layer_code` char(1) DEFAULT NULL,
`data_time` datetime DEFAULT NULL,
`value_int` int(11) DEFAULT NULL,
`value_float` float DEFAULT NULL,
`value_string` varchar(50) DEFAULT NULL,
`re` text);CREATE TABLE `jy_data_real` (
`unit_id` int(11) DEFAULT NULL,
`par_id` int(11) DEFAULT NULL,
`layer_code` char(1) DEFAULT NULL,
`data_time` datetime DEFAULT NULL,
`value_int` int(11) DEFAULT NULL,
`value_float` float DEFAULT NULL,
`value_string` varchar(50) DEFAULT NULL,
`re` text);然后给jy_data_sf6建立触发器
CREATE TRIGGER `jy_data_sf6_before_ins_tr` before INSERT ON `jy_data_sf6` FOR EACH ROW BEGIN
declare rent_count INT;
select count(par_id) into rent_count from jy_data_real where
jy_data_real.par_id = NEW.par_id
and jy_data_real.unit_id = NEW.unit_id;
if rent_count > 0 then
update jy_data_real set
jy_data_real.data_time = new.data_time,
jy_data_real.value_int = new.value_int,
jy_data_real.value_float = new.value_float,
jy_data_real.value_string = new.value_string,
jy_data_real.layer_code = new.layer_code,
jy_data_real.re = new.re where
jy_data_real.par_id = new.par_id
and jy_data_real.unit_id = new.unit_id;
ELSE
insert into jy_data_real (
jy_data_real.par_id,
jy_data_real.unit_id,
jy_data_real.data_time,
jy_data_real.value_int,
jy_data_real.value_float,
jy_data_real.value_string,
jy_data_real.layer_code,
jy_data_real.re
)
values
(
new.par_id,
new.unit_id,
new.data_time,
new.value_int,
new.value_float,
new.value_string,
new.layer_code,
new.re
);
end IF;
END;最后执行sqlINSERT INTO `jy_data_sf6` VALUES ('1183', '49', '44', '2', '2011-05-02 03:34:25', null, '78.61', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1184', '47', '44', '2', '2011-05-02 03:24:25', null, '87.81', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1185', '48', '44', '2', '2011-05-02 03:24:25', null, '63.25', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1186', '49', '44', '2', '2011-05-02 03:24:25', null, '79.29', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1187', '47', '44', '2', '2011-05-02 03:14:25', null, '15.7', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1188', '48', '44', '2', '2011-05-02 03:14:25', null, '18.64', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1189', '49', '44', '2', '2011-05-02 03:14:25', null, '38.27', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1190', '47', '46', '2', '2011-05-02 08:34:37', null, '42.76', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1191', '48', '46', '2', '2011-05-02 08:34:37', null, '31.42', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1192', '49', '46', '2', '2011-05-02 08:34:37', null, '25.13', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1193', '47', '46', '2', '2011-05-02 08:24:37', null, '50.54', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1194', '48', '46', '1', '2011-05-02 08:24:37', null, '9.38', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1195', '49', '46', '2', '2011-05-02 08:24:37', null, '90.46', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1196', '47', '46', '2', '2011-05-02 08:14:37', null, '16.35', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1197', '48', '46', '2', '2011-05-02 08:14:37', null, '15.34', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1198', '49', '46', '2', '2011-05-02 08:14:37', null, '69.12', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1199', '47', '46', '2', '2011-05-02 08:04:37', null, '56.45', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1200', '48', '46', '2', '2011-05-02 08:04:37', null, '43.68', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1201', '49', '46', '2', '2011-05-02 08:04:37', null, '44.78', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1202', '47', '46', '2', '2011-05-02 07:54:37', null, '42.53', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1203', '48', '46', '2', '2011-05-02 07:54:37', null, '76.96', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1204', '49', '46', '2', '2011-05-02 07:54:37', null, '74.01', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1205', '47', '46', '2', '2011-05-02 07:44:37', null, '19.06', null, null);commit;执行的结果查询为:select unit_id, par_id,count(1) from jy_data_real group by unit_id,par_id
unit_id par_id count(1)
44 47 1
44 48 1
44 49 1
46 47 6
46 48 5
46 49 5我想请问为什么会出现这种情况,在触发器中已经 select count(par_id) into rent_count from jy_data_real where
jy_data_real.par_id = NEW.par_id
and jy_data_real.unit_id = NEW.unit_id;
if rent_count > 0 then
判断了,为什么没有更新,还是插入呢
CREATE TABLE `jy_data_sf6` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`par_id` int(11) DEFAULT NULL,
`unit_id` int(11) DEFAULT NULL ,
`layer_code` char(1) DEFAULT NULL,
`data_time` datetime DEFAULT NULL,
`value_int` int(11) DEFAULT NULL,
`value_float` float DEFAULT NULL,
`value_string` varchar(50) DEFAULT NULL,
`re` text);CREATE TABLE `jy_data_real` (
`unit_id` int(11) DEFAULT NULL,
`par_id` int(11) DEFAULT NULL,
`layer_code` char(1) DEFAULT NULL,
`data_time` datetime DEFAULT NULL,
`value_int` int(11) DEFAULT NULL,
`value_float` float DEFAULT NULL,
`value_string` varchar(50) DEFAULT NULL,
`re` text);然后给jy_data_sf6建立触发器
CREATE TRIGGER `jy_data_sf6_before_ins_tr` before INSERT ON `jy_data_sf6` FOR EACH ROW BEGIN
declare rent_count INT;
select count(par_id) into rent_count from jy_data_real where
jy_data_real.par_id = NEW.par_id
and jy_data_real.unit_id = NEW.unit_id;
if rent_count > 0 then
update jy_data_real set
jy_data_real.data_time = new.data_time,
jy_data_real.value_int = new.value_int,
jy_data_real.value_float = new.value_float,
jy_data_real.value_string = new.value_string,
jy_data_real.layer_code = new.layer_code,
jy_data_real.re = new.re where
jy_data_real.par_id = new.par_id
and jy_data_real.unit_id = new.unit_id;
ELSE
insert into jy_data_real (
jy_data_real.par_id,
jy_data_real.unit_id,
jy_data_real.data_time,
jy_data_real.value_int,
jy_data_real.value_float,
jy_data_real.value_string,
jy_data_real.layer_code,
jy_data_real.re
)
values
(
new.par_id,
new.unit_id,
new.data_time,
new.value_int,
new.value_float,
new.value_string,
new.layer_code,
new.re
);
end IF;
END;最后执行sqlINSERT INTO `jy_data_sf6` VALUES ('1183', '49', '44', '2', '2011-05-02 03:34:25', null, '78.61', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1184', '47', '44', '2', '2011-05-02 03:24:25', null, '87.81', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1185', '48', '44', '2', '2011-05-02 03:24:25', null, '63.25', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1186', '49', '44', '2', '2011-05-02 03:24:25', null, '79.29', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1187', '47', '44', '2', '2011-05-02 03:14:25', null, '15.7', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1188', '48', '44', '2', '2011-05-02 03:14:25', null, '18.64', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1189', '49', '44', '2', '2011-05-02 03:14:25', null, '38.27', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1190', '47', '46', '2', '2011-05-02 08:34:37', null, '42.76', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1191', '48', '46', '2', '2011-05-02 08:34:37', null, '31.42', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1192', '49', '46', '2', '2011-05-02 08:34:37', null, '25.13', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1193', '47', '46', '2', '2011-05-02 08:24:37', null, '50.54', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1194', '48', '46', '1', '2011-05-02 08:24:37', null, '9.38', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1195', '49', '46', '2', '2011-05-02 08:24:37', null, '90.46', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1196', '47', '46', '2', '2011-05-02 08:14:37', null, '16.35', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1197', '48', '46', '2', '2011-05-02 08:14:37', null, '15.34', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1198', '49', '46', '2', '2011-05-02 08:14:37', null, '69.12', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1199', '47', '46', '2', '2011-05-02 08:04:37', null, '56.45', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1200', '48', '46', '2', '2011-05-02 08:04:37', null, '43.68', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1201', '49', '46', '2', '2011-05-02 08:04:37', null, '44.78', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1202', '47', '46', '2', '2011-05-02 07:54:37', null, '42.53', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1203', '48', '46', '2', '2011-05-02 07:54:37', null, '76.96', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1204', '49', '46', '2', '2011-05-02 07:54:37', null, '74.01', null, null);commit;
INSERT INTO `jy_data_sf6` VALUES ('1205', '47', '46', '2', '2011-05-02 07:44:37', null, '19.06', null, null);commit;执行的结果查询为:select unit_id, par_id,count(1) from jy_data_real group by unit_id,par_id
unit_id par_id count(1)
44 47 1
44 48 1
44 49 1
46 47 6
46 48 5
46 49 5我想请问为什么会出现这种情况,在触发器中已经 select count(par_id) into rent_count from jy_data_real where
jy_data_real.par_id = NEW.par_id
and jy_data_real.unit_id = NEW.unit_id;
if rent_count > 0 then
判断了,为什么没有更新,还是插入呢
jy_data_real.par_id = NEW.par_id
and jy_data_real.unit_id = NEW.unit_id; 你觉得哪一个 INSERT INTO `jy_data_sf6` VALUES 时没有达到你的预期?
select unit_id, par_id,count(1) from jy_data_real group by unit_id,par_id
unit_id par_id count(1)
44 47 1
44 48 1
44 49 1
46 47 1
46 48 1
46 49 1