area表:DROP TABLE IF EXISTS `area`;
CREATE TABLE `area` (
`rowid` int(11) NOT NULL auto_increment,
`areaname` varchar(30) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
INSERT INTO `area` VALUES ('1', 'CSDN社区');house表:
DROP TABLE IF EXISTS `house`;
CREATE TABLE `house` (
`rowid` bigint(20) NOT NULL auto_increment,
`houseid` varchar(20) default NULL,
`fzname` varchar(20) default NULL,
`usearea` varchar(10) default NULL,
`houproperties` char(1) default NULL COMMENT '0-公1-私', /* 私没有房租 */
`rent` decimal(16,2) default NULL,
`sfyid` bigint(20) default NULL,
`areaid` int(11) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=2670 DEFAULT CHARSET=utf8;INSERT INTO `house` VALUES ('1', '1-1-1', '李四', '42.25', '0', '77.70', null, '', '2');charge表
DROP TABLE IF EXISTS `charge`;
CREATE TABLE `charge` (
`rowid` bigint(20) NOT NULL auto_increment,
`areaid` bigint(20) default NULL,
`hid` varchar(20) default NULL,
`sfqj` varchar(6) default NULL,
`project` char(4) default NULL,
`jcsf` char(1) default NULL,
`jfje` decimal(16,2) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;INSERT INTO `charge` VALUES ('1', '1', '1-1-1', '201106', '1001', '0','77.70');project表
DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
`projectid` char(4) NOT NULL,
`project` varchar(20) default NULL,
`price` decimal(16,2) default NULL,
`jcsf` char(1) default NULL,
PRIMARY KEY (`projectid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `project` VALUES ('1001', '房租', '0.00', '0');
INSERT INTO `project` VALUES ('1002', '设施费', '0.18', '2');
INSERT INTO `project` VALUES ('1003', '卫生费', '6.00', '1');现在又建立了这样的表,根据上面三个表,查询数据插入到这个表里。DROP TABLE IF EXISTS `qftjb`;
CREATE TABLE `qftjb` (
`rowid` int(11) NOT NULL auto_increment,
`areaname` varchar(30) default NULL,
`hid` varchar(20) default NULL,
`fzname` varchar(20) default NULL,
`houproperties` char(1) default NULL COMMENT '0-公1-私',
`projectid` char(4) default NULL,
`project` varchar(20) default NULL,
`Jan` decimal(16,2) default NULL,
`Feb` decimal(16,2) default NULL,
`Mar` decimal(16,2) default NULL,
`Apr` decimal(16,2) default NULL,
`May` decimal(16,2) default NULL,
`Jun` decimal(16,2) default NULL,
`Jul` decimal(16,2) default NULL,
`Aug` decimal(16,2) default NULL,
`Sep` decimal(16,2) default NULL,
`Oct` decimal(16,2) default NULL,
`Nov` decimal(16,2) default NULL,
`Dec` decimal(16,2) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
需要得到的数据:
-- ----------------------------
-- Records of qftjb
-- ----------------------------
INSERT INTO `qftjb` VALUES ('1', 'CSDN社区', '1-1-1', '李四', '公', '1001', '房租', '77.70', '77.70', '77.70', '77.70', '77.70',null,'77.70','77.70','77.70','77.70','77.70','77.70');
INSERT INTO `qftjb` VALUES ('2', 'CSDN社区', '1-1-1', '李四', '公', '1003', '卫生费', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00','6.00','6.00','6.00','6.00','6.00','6.00','6.00');
我的想法:
遍历house表,遍历到第一条,就拿第一条的houseid放到project表里查他应该交的费用的projectid号,同时把金额算出来,然后根据这个结果再放到charge表查有无他的信息,如果一条都没,那么插入欠费表,全部欠费。有记录就根据条件过滤,比如他交几月的,交的什么费。然后再把结果插入欠费表,循环结束.然后再去house表里遍历第二条
...
CREATE TABLE `area` (
`rowid` int(11) NOT NULL auto_increment,
`areaname` varchar(30) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
INSERT INTO `area` VALUES ('1', 'CSDN社区');house表:
DROP TABLE IF EXISTS `house`;
CREATE TABLE `house` (
`rowid` bigint(20) NOT NULL auto_increment,
`houseid` varchar(20) default NULL,
`fzname` varchar(20) default NULL,
`usearea` varchar(10) default NULL,
`houproperties` char(1) default NULL COMMENT '0-公1-私', /* 私没有房租 */
`rent` decimal(16,2) default NULL,
`sfyid` bigint(20) default NULL,
`areaid` int(11) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=2670 DEFAULT CHARSET=utf8;INSERT INTO `house` VALUES ('1', '1-1-1', '李四', '42.25', '0', '77.70', null, '', '2');charge表
DROP TABLE IF EXISTS `charge`;
CREATE TABLE `charge` (
`rowid` bigint(20) NOT NULL auto_increment,
`areaid` bigint(20) default NULL,
`hid` varchar(20) default NULL,
`sfqj` varchar(6) default NULL,
`project` char(4) default NULL,
`jcsf` char(1) default NULL,
`jfje` decimal(16,2) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;INSERT INTO `charge` VALUES ('1', '1', '1-1-1', '201106', '1001', '0','77.70');project表
DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
`projectid` char(4) NOT NULL,
`project` varchar(20) default NULL,
`price` decimal(16,2) default NULL,
`jcsf` char(1) default NULL,
PRIMARY KEY (`projectid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `project` VALUES ('1001', '房租', '0.00', '0');
INSERT INTO `project` VALUES ('1002', '设施费', '0.18', '2');
INSERT INTO `project` VALUES ('1003', '卫生费', '6.00', '1');现在又建立了这样的表,根据上面三个表,查询数据插入到这个表里。DROP TABLE IF EXISTS `qftjb`;
CREATE TABLE `qftjb` (
`rowid` int(11) NOT NULL auto_increment,
`areaname` varchar(30) default NULL,
`hid` varchar(20) default NULL,
`fzname` varchar(20) default NULL,
`houproperties` char(1) default NULL COMMENT '0-公1-私',
`projectid` char(4) default NULL,
`project` varchar(20) default NULL,
`Jan` decimal(16,2) default NULL,
`Feb` decimal(16,2) default NULL,
`Mar` decimal(16,2) default NULL,
`Apr` decimal(16,2) default NULL,
`May` decimal(16,2) default NULL,
`Jun` decimal(16,2) default NULL,
`Jul` decimal(16,2) default NULL,
`Aug` decimal(16,2) default NULL,
`Sep` decimal(16,2) default NULL,
`Oct` decimal(16,2) default NULL,
`Nov` decimal(16,2) default NULL,
`Dec` decimal(16,2) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
需要得到的数据:
-- ----------------------------
-- Records of qftjb
-- ----------------------------
INSERT INTO `qftjb` VALUES ('1', 'CSDN社区', '1-1-1', '李四', '公', '1001', '房租', '77.70', '77.70', '77.70', '77.70', '77.70',null,'77.70','77.70','77.70','77.70','77.70','77.70');
INSERT INTO `qftjb` VALUES ('2', 'CSDN社区', '1-1-1', '李四', '公', '1003', '卫生费', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00','6.00','6.00','6.00','6.00','6.00','6.00','6.00');
我的想法:
遍历house表,遍历到第一条,就拿第一条的houseid放到project表里查他应该交的费用的projectid号,同时把金额算出来,然后根据这个结果再放到charge表查有无他的信息,如果一条都没,那么插入欠费表,全部欠费。有记录就根据条件过滤,比如他交几月的,交的什么费。然后再把结果插入欠费表,循环结束.然后再去house表里遍历第二条
...
INSERT INTO `house` VALUES ('1', '1-1-1', '李四', '42.25', '0', '77.70',null,'1');
心太急了,这种心情希望版主理解下,这次是标点符号问题。INSERT INTO `charge` VALUES ('1', '1', '1-1-1', '201106', '1001', '0','7
7.70');
又多了一条数据,好吧,我错了
人一急就容易犯错误。
INSERT INTO `qftjb` VALUES ('2', 'CSDN社区', '1-1-1', '李四', '公', '1003
', '卫生费', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00','6.00','6.00','6.00'
,'6.00','6.00','6.00');
我又添加了点数据,重新发一下,测试过了。如果很麻烦要写存储过程,还希望版主不吝赐教。
area表:
DROP TABLE IF EXISTS `area`;
CREATE TABLE `area` (
`rowid` int(11) NOT NULL auto_increment,
`areaname` varchar(30) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
INSERT INTO `area` VALUES ('1', 'CSDN社区');
INSERT INTO `area` VALUES ('2', '小区');house表DROP TABLE IF EXISTS `house`;
CREATE TABLE `house` (
`rowid` bigint(20) NOT NULL auto_increment,
`houseid` varchar(20) default NULL,
`fzname` varchar(20) default NULL,
`usearea` varchar(10) default NULL,
`houproperties` char(1) default NULL COMMENT '0-公1-私', /* 私没有房租 */
`rent` decimal(16,2) default NULL,
`sfyid` bigint(20) default NULL,
`areaid` int(11) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=2670 DEFAULT CHARSET=utf8;INSERT INTO `house` VALUES ('1', '1-1-1', '李四', '42.25', '0', '77.70', null, '1');
INSERT INTO `house` VALUES ('2', '2-2-2', '张三', '40.00', '1', null, null, '2');charge表DROP TABLE IF EXISTS `charge`;
CREATE TABLE `charge` (
`rowid` bigint(20) NOT NULL auto_increment,
`areaid` bigint(20) default NULL,
`hid` varchar(20) default NULL,
`sfqj` varchar(6) default NULL,
`project` char(4) default NULL,
`jcsf` char(1) default NULL,
`jfje` decimal(16,2) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;INSERT INTO `charge` VALUES ('1', '1', '1-1-1', '201101', '1001', '0','77.70');
INSERT INTO `charge` VALUES ('2', '1', '1-1-1', '201102', '1001', '0','77.70');
INSERT INTO `charge` VALUES ('3', '1', '1-1-1', '201103', '1001', '0','77.70');
INSERT INTO `charge` VALUES ('4', '1', '1-1-1', '201104', '1001', '0','77.70');
INSERT INTO `charge` VALUES ('5', '1', '1-1-1', '201105', '1001', '0','77.70');
INSERT INTO `charge` VALUES ('6', '1', '1-1-1', '201106', '1001', '0','77.70');
INSERT INTO `charge` VALUES ('7', '1', '1-1-1', '201107', '1001', '0','77.70');
INSERT INTO `charge` VALUES ('8', '1', '1-1-1', '201108', '1001', '0','77.70');
INSERT INTO `charge` VALUES ('9', '1', '1-1-1', '201109', '1001', '0','77.70');
INSERT INTO `charge` VALUES ('10', '1', '1-1-1', '201110', '1001', '0','77.70');
INSERT INTO `charge` VALUES ('11', '1', '1-1-1', '201111', '1001', '0','77.70');
INSERT INTO `charge` VALUES ('12', '1', '1-1-1', '201106', '1003', '1','6.0');
INSERT INTO `charge` VALUES ('13', '2', '2-2-2', '201101', '1002', '2','7.2');project表
DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
`projectid` char(4) NOT NULL,
`project` varchar(20) default NULL,
`price` decimal(16,2) default NULL,
`jcsf` char(1) default NULL,
PRIMARY KEY (`projectid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `project` VALUES ('1001', '房租', '0.00', '0');
INSERT INTO `project` VALUES ('1002', '设施费', '0.18', '2');
INSERT INTO `project` VALUES ('1003', '卫生费', '6.00', '1');qftjb表DROP TABLE IF EXISTS `qftjb`;
CREATE TABLE `qftjb` (
`rowid` int(11) NOT NULL auto_increment,
`areaid` int(11) default NULL,
`areaname` varchar(30) default NULL,
`hid` varchar(20) default NULL,
`fzname` varchar(20) default NULL,
`houproperties` char(1) default NULL COMMENT '0-公1-私',
`projectid` char(4) default NULL,
`project` varchar(20) default NULL,
`Jan` decimal(16,2) default NULL,
`Feb` decimal(16,2) default NULL,
`Mar` decimal(16,2) default NULL,
`Apr` decimal(16,2) default NULL,
`May` decimal(16,2) default NULL,
`Jun` decimal(16,2) default NULL,
`Jul` decimal(16,2) default NULL,
`Aug` decimal(16,2) default NULL,
`Sep` decimal(16,2) default NULL,
`Oct` decimal(16,2) default NULL,
`Nov` decimal(16,2) default NULL,
`Dec` decimal(16,2) default NULL,
PRIMARY KEY (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;--------------分割线----------------
我的需求就是根据上面4张表,得到下面的数据,然后插入--qftjb表--我的想法和流程上面发了,不知道怎么去实现。
INSERT INTO `qftjb` VALUES ('1', '1','CSDN社区', '1-1-1', '李四', '公', '1001', '房租', '77.70', '77.70', '77.70', '77.70', '77.70','77.70','77.70','77.70','77.70','77.70','77.70',null);
INSERT INTO `qftjb` VALUES ('2', '1','CSDN社区', '1-1-1', '李四', '公', '1003', '卫生费','6.00', '6.00', '6.00', '6.00', '6.00',null,'6.00','6.00','6.00','6.00','6.00','6.00');
INSERT INTO `qftjb` VALUES ('3', '2','小区', '2-2-2', '张三', '私', '1002', '设施费',null, '7.2', '7.2','7.2','7.2','7.2','7.2','7.2','7.2','7.2','7.2','7.2');
1001', '房租', '77.70', '77.70', '77.70', '77.70', '77.70','77.70','77.70','77.7
0','77.70','77.70','77.70',null);
ERROR 1366 (HY000): Incorrect string value: '\xC9\xE7\xC7\xF8' for column 'arean
ame' at row 1
mysql>
1001', '房租', '77.70', '77.70', '77.70', '77.70', '77.70','77.70','77.70','77.7
0','77.70','77.70','77.70',null);
ERROR 1366 (HY000): Incorrect string value: '\xC9\xE7\xC7\xF8' for column 'arean
ame' at row 1
mysql>
受影响的行: 1
时间: 0.047ms