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(+)