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(+)
谁能把这句代码优化下,有助于提高执行效率的给分
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(+)
谁能把这句代码优化下,有助于提高执行效率的给分
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(+);
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(+)