SELECT a.lsh_xm, a.xmmc, a.sfqy, a.migrate_date, b.fangwu, b.diya,
b.chafeng,
b.chanquan
FROM xm_xxk a,
      (SELECT lsh_xm,
              sum(decode(ywlx,0,1,0)) fangwu,
              sum(decode(ywlx,2,1,0)) diya,
              sum(decode(ywlx,3,1,0)) chafeng,
              sum(decode(ywlx,4,1,0)) chanquan
      FROM big_house
      WHERE isvalid = 1
      GROUP BY lsh_xm) b
WHERE a.lsh_xm = b.lsh_xm(+);

------解决方案--------------------

虽然你没有贴执行计划,但是从逻辑上简化下还是可以的,如果想要提高效率还需要改善,先贴执行计划吧
SQL code
SELECT a.lsh_xm, a.xmmc, a.sfqy, a.migrate_date, b.fangwu, b.diya, b.chafeng,
       b.chanquan
  FROM xm_xxk a,
       (SELECT   lsh_xm, COUNT (DECODE (ywlx, 0, 1, NULL)) AS fangwu,
                 COUNT (DECODE (ywlx, 2, 1, NULL)) AS diya,
                 COUNT (DECODE (ywlx, 3, 1, NULL)) AS chafeng,
                 COUNT (DECODE (ywlx, 4, 1, NULL)) AS chanquan
            FROM big_house
           WHERE ywlx IN (0, 2, 3, 4) AND isvalid = 1
        GROUP BY lsh_xm) b
 WHERE a.lsh_xm = b.lsh_xm(+)