SELECT a.prjid,a.PROJECTNAME,zhuzhai,shangye,(zongji-zhuzhai-shangye) qita FROM (SELECT prjid ,PROJECTNAME,COUNT(*) zhuzhai FROM HOUSEWINDOW WHERE USAGE like '%住宅%' AND presaleid=0 AND SALEID!=2 AND MORTID<=0 AND LIMITID<=0 GROUP BY prjid,PROJECTNAME) a RIGHT JOIN (SELECT prjid ,COUNT(*) shangye FROM HOUSEWINDOW WHERE USAGE like '%商业%' AND presaleid=0 AND SALEID!=2 AND MORTID<=0 AND LIMITID<=0 GROUP BY prjid) b RIGHT JOIN (SELECT prjid ,COUNT(*) zongji FROM HOUSEWINDOW WHERE presaleid=0 AND SALEID!=2 AND MORTID<=0 AND LIMITID<=0 GROUP BY prjid) c RIGHT JOIN (SELECT prjid FROM project_more WHERE STAT=1) d ON c.prjid=d.prjid ON b.prjid=d.prjid ON a.prjid=d.prjid;
decode(zhuzhai,null,随便什么,zhuzhai)
(SELECT prjid ,PROJECTNAME,COUNT(*) zhuzhai FROM HOUSEWINDOW WHERE USAGE like '%住宅%' AND presaleid=0 AND SALEID!=2 AND MORTID<=0 AND LIMITID<=0 GROUP BY prjid,PROJECTNAME) a
RIGHT JOIN
(SELECT prjid ,COUNT(*) shangye FROM HOUSEWINDOW WHERE USAGE like '%商业%' AND presaleid=0 AND SALEID!=2 AND MORTID<=0 AND LIMITID<=0 GROUP BY prjid) b
RIGHT JOIN
(SELECT prjid ,COUNT(*) zongji FROM HOUSEWINDOW WHERE presaleid=0 AND SALEID!=2 AND MORTID<=0 AND LIMITID<=0 GROUP BY prjid) c
RIGHT JOIN
(SELECT prjid FROM project_more WHERE STAT=1) d
ON c.prjid=d.prjid
ON b.prjid=d.prjid
ON a.prjid=d.prjid;