请问高手,这个SQL有没有更好的写法,请指教!SELECT A.*,B.XF_Freezeqty
FROM
(
select distinct h.XF_wsallocloc,d.XF_PLU
from XF_WSCUSTORDERD d,
xf_wscustorderh h
where h.xf_wsorderloc = d.xf_wsorderloc
and h.xf_wsorderno = d.xf_wsorderno
) A
LEFT OUTER JOIN
XF_ITEMEXTSTAT B
ON A.XF_wsallocloc=B.XF_STORECODE
AND A.XF_PLU = B.XF_PLU
FROM
(
select distinct h.XF_wsallocloc,d.XF_PLU
from XF_WSCUSTORDERD d,
xf_wscustorderh h
where h.xf_wsorderloc = d.xf_wsorderloc
and h.xf_wsorderno = d.xf_wsorderno
) A
LEFT OUTER JOIN
XF_ITEMEXTSTAT B
ON A.XF_wsallocloc=B.XF_STORECODE
AND A.XF_PLU = B.XF_PLU
从代码可看性
可以这样
SELECT a.*, b.xf_freezeqty
FROM (SELECT DISTINCT h.xf_wsallocloc, d.xf_plu
FROM xf_wscustorderd d, xf_wscustorderh h
WHERE h.xf_wsorderloc = d.xf_wsorderloc
AND h.xf_wsorderno = d.xf_wsorderno) a,
xf_itemextstat b
WHERE a.xf_wsallocloc = b.xf_storecode(+) AND a.xf_plu = b.xf_plu(+)
http://space.itpub.net/10768286/viewspace-243950
group by 要比distinct慢一点