insert into qftjb (areaid,areaname,hid,year,fzname,houproperties,projectid,project,sfyid,projectfee,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dece,total)
Select areaid, areaname, houseid as hid, year, fzname,if(houproperties='0','公','私') as houproperties,projectid,project,sfyid,FORMAT(projectfee,2) as projectfee,
    FORMAT(projectfee - Jan, 2) As Jan, FORMAT(projectfee - Feb, 2) As Feb, FORMAT(projectfee - Mar, 2) As Mar, FORMAT(projectfee - Apr, 2) As Apr,
    FORMAT(projectfee - May, 2) As May, FORMAT(projectfee - Jun, 2) As Jun, FORMAT(projectfee - Jul, 2) As Jul, FORMAT(projectfee - Aug, 2) As Aug,
    FORMAT(projectfee - Sep, 2) As Sep, FORMAT(projectfee - Oct, 2) As Oct, FORMAT(projectfee - Nov, 2) As Nov, FORMAT(projectfee - Dece, 2) As Dece,
    FORMAT(projectfee*12 - (Case When sumyear is null Then 0 Else sumyear End), 2) As total
From (
  Select tmpMain.*, areaname, fzname,houproperties,project,sfyid,
         (Case tmpMain.projectid When '1001' Then rent When '1002' Then price*constarea Else price End) As projectfee /* 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 houseid,fzname,usearea,houproperties,sfyid,rent,areaid,year From house, ( Select distinct year From ( Select distinct SUBSTR(sfqj, 1, 4) as year From charge Union All  Select CAST(YEAR(sysdate()) As CHAR) as year ) tmp11 /* Get all years, and include current year */) 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 */
) tmpAll
/* Here you can write some condition    */
Order By areaid, houseid, year, projectid;

INSERT INTO `qftjb` VALUES ('37', '1', '南小巷', 'houseid', '2012', '李敏', '公', '1001', '房租', null, '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '13