这样的查询语句怎么写？
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表--
（因为每户人房租不一样，设施费不一样，卫生费不一样，条件比较复杂，而且charge表是收费信息，但是需要得到欠费信息，所以比较难，请教一下。）
INSERT INTO `qftjb` VALUES ('1', '1','csdn', '1-1-1', '李四', '公', '1001', '房租', null, null, null, null, null, null, null, null, null, null, null, '77.70');
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','bbs', '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');
[解决办法]
mysql> select h.areaid,a.areaname,c.hid,h.fzname,h.houproperties, -> c.project,p.project, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201101',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201101',jfje,0)),0) as A01, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201102',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201102',jfje,0)),0) as A02, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201103',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201103',jfje,0)),0) as A03, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201104',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201104',jfje,0)),0) as A04, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201105',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201105',jfje,0)),0) as A05, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201106',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201106',jfje,0)),0) as A06, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201107',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201107',jfje,0)),0) as A07, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201108',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201108',jfje,0)),0) as A08, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201109',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201109',jfje,0)),0) as A09, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201110',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201110',jfje,0)),0) as A10, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201111',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201111',jfje,0)),0) as A11, -> if((select min(sfqj) from charge where areaid=c.areaid)<='201112',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201112',jfje,0)),0) as A12 -> from house h,area a,charge c,project p -> where h.areaid=a.rowid -> and h.areaid=c.areaid -> and c.project=p.projectid -> group by h.areaid,a.areaname,c.hid,h.fzname,h.houproperties, -> c.project,p.project;+--------+----------+-------+--------+---------------+---------+---------+------+------+------+------+------+------+------+------+------+------+------+-------+| areaid | areaname | hid | fzname | houproperties | project | project | A01 | A02 | A03 | A04 | A05 | A06 | A07 | A08 | A09 | A10 | A11 | A12 |+--------+----------+-------+--------+---------------+---------+---------+------+------+------+------+------+------+------+------+------+------+------+-------+| 1 | CSDN社区 | 1-1-1 | 李四 | 0 | 1001 | 房租 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 77.70 || 1 | CSDN社区 | 1-1-1 | 李四 | 0 | 1003 | 卫生费 | 6.00 | 6.00 | 6.00 | 6.00 | 6.00 | 0.00 | 6.00 | 6.00 | 6.00 | 6.00 | 6.00 | 6.00 || 2 | 小区 | 2-2-2 | 张三 | 1 | 1002 | 设施费 | 0.00 | 7.20 | 7.20 | 7.20 | 7.20 | 7.20 | 7.20 | 7.20 | 7.20 | 7.20 | 7.20 | 7.20 |+--------+----------+-------+--------+---------------+---------+---------+------+------+------+------+------+------+------+------+------+------+------+-------+3 rows in set (0.00 sec)mysql>
[解决办法]
经过跟楼主的共同努力，得到如下结果（话说附加要求还是挺多的）：
Select areaid, areaname, houseid, year, fzname, (Case houproperties When '0' Then '公' Else '私' End) As HP, projectid, project, SR, ROUND(SR - Jan, 2) As Jan, ROUND(SR - Feb, 2) As Feb, ROUND(SR - Mar, 2) As Mar, ROUND(SR - Apr, 2) As Apr, ROUND(SR - May, 2) As May, ROUND(SR - Jun, 2) As Jun, ROUND(SR - Jul, 2) As Jul, ROUND(SR - Aug, 2) As Aug, ROUND(SR - Sep, 2) As Sep, ROUND(SR - Oct, 2) As Oct, ROUND(SR - Nov, 2) As Nov, ROUND(SR - Dece, 2) As Dece, ROUND(SR*12 - (Case When sumyear is null Then 0 Else sumyear End), 2) As YearDebtFrom ( Select tmpMain.*, areaname, fzname, houproperties, project, (Case tmpMain.projectid When '1001' Then rent When '1002' Then price*usearea Else price End) As SR /* Calculate the ShouldRent */ From ( Select h.areaid, h.houseid, h.year, projectid, SUM(Case SUBSTR(sfqj, -2) When '01' Then jfje Else 0 End) As Jan, SUM(Case SUBSTR(sfqj, -2) When '02' Then jfje Else 0 End) As Feb, SUM(Case SUBSTR(sfqj, -2) When '03' Then jfje Else 0 End) As Mar, SUM(Case SUBSTR(sfqj, -2) When '04' Then jfje Else 0 End) As Apr, SUM(Case SUBSTR(sfqj, -2) When '05' Then jfje Else 0 End) As May, SUM(Case SUBSTR(sfqj, -2) When '06' Then jfje Else 0 End) As Jun, SUM(Case SUBSTR(sfqj, -2) When '07' Then jfje Else 0 End) As Jul, SUM(Case SUBSTR(sfqj, -2) When '08' Then jfje Else 0 End) As Aug, SUM(Case SUBSTR(sfqj, -2) When '09' Then jfje Else 0 End) As Sep, SUM(Case SUBSTR(sfqj, -2) When '10' Then jfje Else 0 End) As Oct, SUM(Case SUBSTR(sfqj, -2) When '11' Then jfje Else 0 End) As Nov, SUM(Case SUBSTR(sfqj, -2) When '12' Then jfje Else 0 End) As Dece, SUM(jfje) As sumyear /* All rent in the year */ From ( Select * From ( Select * From house, (Select distinct SUBSTR(sfqj, 1, 4) as year From charge) tmp1 /* That's cross join, try to get all years */ ) hh, project p /* That's cross join, try to get all projects */ Where ((hh.houproperties = '0' and (projectid ='1001' or projectid = '1003')) /* Deal with the relationship between houproperties and projectid */ or (hh.houproperties = '1' and projectid ='1002')) ) h Left Outer Join charge c On c.areaid = h.areaid and c.hid = h.houseid and c.project = h.projectid and h.year = SUBSTR(sfqj, 1, 4) Group By h.areaid, h.houseid, h.year, projectid ) tmpMain /* Here we got the main result */ Left Outer Join house hh On tmpMain.areaid = hh.areaid and tmpMain.houseid = hh.houseid /* For calculate the ShouldRent */ Left Outer Join project pp On tmpMain.projectid = pp.projectid /* For calculate the ShouldRent, too */ Join area a On tmpMain.areaid = a.rowid /* For get the area's name */) tmpAllOrder By areaid, houseid, year, projectid;
