当前位置: 代码迷 >> Java Web开发 >> 看看这个mysql语句如何写,或者存储过程
  详细解决方案

看看这个mysql语句如何写,或者存储过程

热度:154   发布时间:2016-04-17 10:57:14.0
看看这个mysql语句怎么写,或者存储过程
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,
  相关解决方案