SQL代码优化
SELECT a.lsh_xm, a.xmmc, a.sfqy, a.migrate_date, b.fangwu, c.diya,
d.chafeng,
e.chanquan
FROM
xm_xxk a,
(SELECT lsh_xm, COUNT (1) AS fangwu
FROM big_house
WHERE ywlx =
0 AND isvalid = 1
GROUP BY lsh_xm) b,
(SELECT lsh_xm, COUNT (1) AS diya
FROM big_house
WHERE ywlx = 2 AND isvalid = 1
GROUP BY lsh_xm) c,
(SELECT lsh_xm, COUNT (1) AS chafeng
FROM big_house
WHERE ywlx = 3 AND
isvalid = 1
GROUP BY lsh_xm) d,
(SELECT lsh_xm, COUNT (1) AS chanquan
FROM big_house
WHERE ywlx = 4 AND isvalid=1
GROUP BY lsh_xm) e,
WHERE a.lsh_xm = b.lsh_xm(+)
AND a.lsh_xm = c.lsh_xm(+)
AND
a.lsh_xm = d.lsh_xm(+)
AND a.lsh_xm = e.lsh_xm(+)

谁能把这句代码优化下，有助于提高执行效率的给分

[解决办法]

SQL code

SELECT a.lsh_xm, a.xmmc, a.sfqy, a.migrate_date, b.fangwu, b.diya,b.chafeng,b.chanquanFROM 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) bWHERE 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(+)