这样的查询语句如何写

这样的查询语句怎么写？
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');

[解决办法]

SQL code

mysql&gt; select h.areaid,a.areaname,c.hid,h.fzname,h.houproperties,   -&gt;  c.project,p.project,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201101',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201101',jfje,0)),0) as A01,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201102',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201102',jfje,0)),0) as A02,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201103',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201103',jfje,0)),0) as A03,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201104',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201104',jfje,0)),0) as A04,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201105',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201105',jfje,0)),0) as A05,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201106',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201106',jfje,0)),0) as A06,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201107',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201107',jfje,0)),0) as A07,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201108',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201108',jfje,0)),0) as A08,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201109',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201109',jfje,0)),0) as A09,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201110',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201110',jfje,0)),0) as A10,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201111',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201111',jfje,0)),0) as A11,   -&gt;  if((select min(sfqj) from charge where areaid=c.areaid)&lt;='201112',(select min(jfje) from charge where areaid=c.areaid and project=c.project)-sum(if(sfqj='201112',jfje,0)),0) as A12   -&gt; from house h,area a,charge c,project p   -&gt; where h.areaid=a.rowid   -&gt; and h.areaid=c.areaid   -&gt; and c.project=p.projectid   -&gt; group by h.areaid,a.areaname,c.hid,h.fzname,h.houproperties,   -&gt;  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&gt; 
 
[解决办法]
经过跟楼主的共同努力，得到如下结果（话说附加要求还是挺多的）：
SQL codeSelect 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;

这样的查询语句如何写

热点推荐