首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > Mysql >

这样的查询语句如何写

2012-03-17 
这样的查询语句怎么写?area表:DROP TABLE IF EXISTS `area`CREATE TABLE `area` (`rowid` int(11) NOT NU

这样的查询语句怎么写?
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> 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> 


[解决办法]
经过跟楼主的共同努力,得到如下结果(话说附加要求还是挺多的):

SQL code
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; 

热点排行